Avec PostgreSQL 15 ou une version ultérieure, utilisez une contrainte UNIQUE NULLS NOT DISTINCT déclarée DEFERRABLE, puis différez-la dans la transaction de réordonnancement. Cela permet aux lignes d’articles d’échanger leurs positions tout en garantissant un ordre final unique, y compris pour les lignes dont le parent est NULL. Syntaxe des contraintes PostgreSQL

Ce tutoriel s’adresse aux développeurs travaillant dans le Terminal de Mac et qui doivent réordonner des lignes d’articles synthétiques au sein d’un même groupe parent. Il comprend une démonstration SQL, un programme complet avec node-postgres qui valide l’ordre demandé, ainsi qu’une solution de remplacement pour PostgreSQL 14. Le changement de parent des articles n’entre pas dans le périmètre de cet exemple.

Pourquoi un parent nullable complique-t-il l’unicité des positions ?

Supposons que chaque article ait un id, un parent_id nullable et un position entier positif. Les articles ayant le même parent forment un groupe de frères ordonné. Dans ce modèle, tous les articles dont le parent est null appartiennent à un seul groupe racine.

Un UNIQUE (parent_id, position) classique ne garantit pas cette règle pour le groupe racine. PostgreSQL considère normalement que les valeurs null sont distinctes lors du contrôle d’unicité : deux articles racines peuvent donc tous deux occuper la position 1. L’ajout de NULLS NOT DISTINCT fait que ces valeurs de parent null sont considérées comme identiques lors de la vérification de la clé composite. Des parents non nuls différents peuvent toujours avoir chacun leur propre position 1. PostgreSQL : contraintes d’unicité

Le réordonnancement pose aussi un problème de temporisation distinct. Si l’article 1 occupe la position 1 et que l’article 2 occupe la position 2, déplacer l’article 2 vers la position 1 duplique temporairement la position de l’article 1. Une contrainte d’unicité non différable est vérifiée immédiatement à chaque insertion ou modification de ligne. Une contrainte différable peut attendre la fin de l’instruction ou, si elle est explicitement différée, la fin de la transaction. PostgreSQL : CREATE TABLE

Pour un réordonnancement réalisé par plusieurs mises à jour, ces deux propriétés comptent : l’unicité qui tient compte des valeurs null définit les groupes corrects, et le contrôle différé permet les collisions temporaires entre les mises à jour.

Quelle définition utiliser selon votre version de PostgreSQL ?

PostgreSQL 15 a introduit NULLS NOT DISTINCT pour les contraintes et les index d’unicité. Les principaux exemples ci-dessous nécessitent la version 15 ou une version ultérieure du serveur ; PostgreSQL 14 requiert la solution de remplacement présentée plus loin. Vérifiez la version du serveur auquel vous êtes connecté au lieu de la déduire de celle du client en ligne de commande installé. Notes de version de PostgreSQL 15

Situation Approche prise en charge
PostgreSQL 15 ou version ultérieure, nouvelle table Contrainte nommée UNIQUE NULLS NOT DISTINCT avec DEFERRABLE INITIALLY IMMEDIATE
Contrainte d’unicité classique existante Remplacez sa définition ; ALTER CONSTRAINT ne permet pas de modifier le caractère différable d’une contrainte d’unicité
Index d’unicité partiel ou sur expression existant Remplacez la méthode de contrôle ; il ne peut pas être rattaché comme contrainte d’unicité classique
PostgreSQL 14 Utilisez des colonnes de regroupement générées avec une contrainte d’unicité différable

Les limites concernant les objets existants sont documentées dans ALTER TABLE : la modification des attributs de contrainte de cette manière est limitée aux clés étrangères, et un index rattaché en tant que contrainte d’unicité ne peut contenir d’expressions ni être partiel. Une commande autonome CREATE UNIQUE INDEX ne comporte pas non plus de clause DEFERRABLE. PostgreSQL : ALTER TABLE, PostgreSQL : CREATE INDEX

Comment permuter deux articles racines en SQL ?

Prérequis : une base de données PostgreSQL locale existante en version 15 ou ultérieure et une connexion psql fonctionnelle. Ouvrez le Terminal de Mac et lancez le client avec vos paramètres de connexion configurés. La commande suivante ignore les personnalisations de démarrage et interrompt le traitement du script en cas d’erreur. PostgreSQL : psql

psql -X -v ON_ERROR_STOP=1

Collez la démonstration SQL complète ci-dessous dans cette session. Elle crée une table temporaire qui ne contient que des articles synthétiques ; les tables temporaires disparaissent à la fin de la session. Le nom de la table ne doit pas déjà être utilisé dans cette session. PostgreSQL : tables temporaires

SHOW server_version;

CREATE TEMP TABLE article_order_demo (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES article_order_demo(id),
    title text NOT NULL,
    position integer NOT NULL CHECK (position > 0),
    CONSTRAINT article_order_demo_parent_position_key
        UNIQUE NULLS NOT DISTINCT (parent_id, position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO article_order_demo (id, parent_id, title, position)
VALUES
    (1, NULL, 'Article A', 1),
    (2, NULL, 'Article B', 2),
    (3, NULL, 'Article C', 3),
    (4, 1, 'Article A: Overview', 1),
    (5, 1, 'Article A: Examples', 2);

BEGIN;
SET CONSTRAINTS article_order_demo_parent_position_key DEFERRED;

UPDATE article_order_demo
SET position = 1
WHERE id = 2 AND parent_id IS NULL;

UPDATE article_order_demo
SET position = 2
WHERE id = 1 AND parent_id IS NULL;

SET CONSTRAINTS article_order_demo_parent_position_key IMMEDIATE;
COMMIT;

SELECT id, title, position
FROM article_order_demo
WHERE parent_id IS NULL
ORDER BY position;

Après la première mise à jour, la transaction contient temporairement deux racines à la position 1. Après la deuxième mise à jour, leurs positions sont de nouveau uniques. Le retour de la contrainte à IMMEDIATE vérifie alors les modifications en attente. Si un doublon subsiste, cette commande échoue ; sinon, la transaction peut être validée. Le report change le moment où la validation a lieu. PostgreSQL : SET CONSTRAINTS

L’ordre final attendu des racines est le suivant :

Position ID de l’article Titre
1 2 Article B
2 1 Article A
3 3 Article C

Les articles enfants conservent leurs positions, car aucune des deux mises à jour ne les sélectionne. Si une transaction interactive rencontre une erreur, exécutez ROLLBACK; avant de réessayer. Une transaction regroupe les instructions connexes jusqu’à COMMIT ou ROLLBACK. PostgreSQL : BEGIN

Comment réordonner un groupe complet de frères avec node-postgres ?

Utilisez un seul client réservé pour toute la transaction. node-postgres l’exige explicitement : plusieurs appels à pool.query() ne conviennent pas à une transaction, car ils peuvent être exécutés sur des connexions différentes. node-postgres : transactions

Le programme ci-dessous accepte une liste complète et ordonnée d’ID de frères. Il applique une règle supplémentaire simple : la liste fournie doit être une permutation de l’ensemble actuel des frères. Le rejet des doublons, des omissions et des ID provenant d’autres groupes évite qu’un réordonnancement partiel semble avoir réussi.

Dans un projet Node.js local, installez pg, enregistrez le programme complet suivant sous le nom reorder.cjs, puis exécutez-le. Le programme suppose que les paramètres de connexion locale existants sont disponibles via des variables d’environnement PostgreSQL. node-postgres prend en charge les mêmes variables d’environnement de connexion que psql. node-postgres : connexion

npm install pg
node reorder.cjs

const { Pool } = require('pg');

function validateOrder(ids, rows) {
  if (!Array.isArray(ids) || !ids.every(Number.isSafeInteger) ||
      new Set(ids).size !== ids.length) {
    throw new Error('Order must contain distinct integer IDs');
  }
  const existing = new Set(rows.map(row => row.id));
  if (rows.length !== ids.length || ids.some(id => !existing.has(id))) {
    throw new Error('Order must include every sibling exactly once');
  }
}

async function main() {
  const pool = new Pool();
  let client;
  try {
    client = await pool.connect();
    await client.query(`
      CREATE TEMP TABLE article_order_demo (
        id integer PRIMARY KEY,
        parent_id integer REFERENCES article_order_demo(id),
        title text NOT NULL,
        position integer NOT NULL CHECK (position > 0),
        CONSTRAINT article_order_demo_parent_position_key
          UNIQUE NULLS NOT DISTINCT (parent_id, position)
          DEFERRABLE INITIALLY IMMEDIATE
      )
    `);
    await client.query(`
      INSERT INTO article_order_demo (id, parent_id, title, position)
      VALUES (1, NULL, 'Article A', 1),
             (2, NULL, 'Article B', 2),
             (3, NULL, 'Article C', 3),
             (4, 1, 'Article A: Overview', 1),
             (5, 1, 'Article A: Examples', 2)
    `);

    const parentId = null;
    const orderedIds = [2, 1, 3];
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    try {
      await client.query(
        'LOCK TABLE article_order_demo IN SHARE ROW EXCLUSIVE MODE'
      );
      const siblings = await client.query(
        `SELECT id FROM article_order_demo
         WHERE parent_id IS NOT DISTINCT FROM $1::integer`,
        [parentId]
      );
      validateOrder(orderedIds, siblings.rows);
      await client.query(
        'SET CONSTRAINTS article_order_demo_parent_position_key DEFERRED'
      );
      for (const [index, id] of orderedIds.entries()) {
        const result = await client.query(
          `UPDATE article_order_demo SET position = $1
           WHERE id = $2
             AND parent_id IS NOT DISTINCT FROM $3::integer`,
          [index + 1, id, parentId]
        );
        if (result.rowCount !== 1) {
          throw new Error('Expected exactly one updated article');
        }
      }
      await client.query(
        'SET CONSTRAINTS article_order_demo_parent_position_key IMMEDIATE'
      );
      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    }

    const result = await client.query(
      `SELECT id, title, position FROM article_order_demo
       WHERE parent_id IS NOT DISTINCT FROM $1::integer
       ORDER BY position`,
      [parentId]
    );
    console.table(result.rows);
  } finally {
    if (client) client.release();
    await pool.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

IS NOT DISTINCT FROM permet au filtre sur le parent de fonctionner pour les valeurs nulles comme pour les valeurs non nulles. Une égalité ordinaire avec un paramètre null ne sélectionne pas les racines. Pour réordonner les enfants de l’exemple, remplacez parentId par 1 et orderedIds par [5, 4]. PostgreSQL : fonctions et opérateurs de comparaison

Les paramètres de type $1 transmettent les valeurs séparément du texte SQL. Le transtypage explicite en entier indique également le type attendu lorsque le paramètre parent est null. node-postgres : requêtes

Pourquoi obtenir le verrou avant de lire les frères ?

La démonstration temporaire n’implique aucune session concurrente. Le verrou de table illustre une méthode volontairement large pour une petite table partagée : SHARE ROW EXCLUSIVE entre en conflit avec les écritures normales sur la table et avec lui-même, tout en autorisant les lectures ordinaires. Le conserver pendant toute la transaction stabilise l’appartenance au groupe des frères durant la validation et les mises à jour. Il bloque aussi les écritures concernant des groupes parents sans rapport ; évaluez ce compromis avant de l’utiliser sur une table très sollicitée. PostgreSQL : verrouillage explicite

Le programme sélectionne explicitement READ COMMITTED. À ce niveau d’isolation, la requête sur les frères voit les modifications validées jusqu’au début de cette requête, y compris celles validées avant la fin d’une attente précédente pour obtenir un verrou. PostgreSQL : BEGIN, PostgreSQL : isolation des transactions

La validation JavaScript a été vérifiée avec une permutation valide ainsi qu’avec des ID en double, manquants et appartenant à un autre groupe. Ces vérifications déterministes ont réussi. La documentation a permis d’examiner les exemples de base de données, mais ils n’ont pas été exécutés sur PostgreSQL dans l’environnement de recherche.

Quelle solution est prise en charge dans PostgreSQL 14 ?

Dans PostgreSQL 14, matérialisez l’expression de regroupement au moyen de colonnes générées, puis appliquez une contrainte d’unicité différable à ces colonnes. Les colonnes générées stockées sont calculées lors de l’écriture des lignes et ne peuvent pas être remplacées par des valeurs ordinaires. PostgreSQL 14 : colonnes générées

Cet exemple SQL autonome utilise deux valeurs générées pour représenter le groupe parent. La valeur booléenne distingue les racines d’un véritable ID parent égal à 0 ; la valeur de repli COALESCE n’exige donc pas de réserver un ID.

CREATE TEMP TABLE article_order_demo14 (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES article_order_demo14(id),
    title text NOT NULL,
    position integer NOT NULL CHECK (position > 0),
    parent_is_root boolean
        GENERATED ALWAYS AS (parent_id IS NULL) STORED,
    parent_group integer
        GENERATED ALWAYS AS (COALESCE(parent_id, 0)) STORED,
    CONSTRAINT article_order_demo14_parent_position_key
        UNIQUE (parent_is_root, parent_group, position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO article_order_demo14 (id, parent_id, title, position)
VALUES (1, NULL, 'Article A', 1),
       (2, NULL, 'Article B', 2);

BEGIN;
SET CONSTRAINTS article_order_demo14_parent_position_key DEFERRED;
UPDATE article_order_demo14 SET position = 1 WHERE id = 2;
UPDATE article_order_demo14 SET position = 2 WHERE id = 1;
SET CONSTRAINTS article_order_demo14_parent_position_key IMMEDIATE;
COMMIT;

SELECT id, title, position
FROM article_order_demo14
WHERE parent_id IS NULL
ORDER BY position;

Pour une racine, les valeurs de regroupement générées sont (true, 0) ; pour le parent 0, elles sont (false, 0). Chaque parent non nul conserve sa propre valeur entière. La contrainte exprime donc le regroupement voulu sans dépendre du traitement des valeurs null de PostgreSQL 15. PostgreSQL 14 prend en charge les contraintes d’unicité différables portant sur des colonnes nommées. PostgreSQL 14 : CREATE TABLE

Que faut-il vérifier avant d’adapter une table existante ?

Un index sur expression portant sur COALESCE(parent_id, 0) peut imposer l’unicité à partir d’une expression, mais il ne peut pas simplement devenir différable ni être rattaché comme contrainte d’unicité classique. Un index partiel sur les lignes racines présente la même limite de rattachement. Si l’un de ces objets de contrôle immédiat reste en place, il peut toujours empêcher une permutation après l’ajout d’une contrainte différable. PostgreSQL : index sur les expressions, PostgreSQL : ALTER TABLE

Avant de remplacer une définition existante, recherchez les paires parent-position en double, en particulier pour les racines que l’unicité nullable classique autorisait auparavant. L’ajout d’une contrainte d’unicité valide les lignes existantes ; NOT VALID n’est pas disponible pour les contraintes d’unicité. Résolvez délibérément les positions ambiguës avant de tenter la modification. PostgreSQL : ALTER TABLE

Distinguez la garantie de la contrainte de la règle d’ordre de l’application. L’unicité seule autorise les positions discontinues, comme 1, 4 et 9. Le programme qui traite la liste complète attribue volontairement des positions consécutives à partir de 1. C’est son contrôle de l’appartenance au groupe qui garantit la participation de chaque frère.

Pour cette tâche, choisissez la contrainte qui représente les groupes à parent nullable, différez-la avant la première mise à jour entraînant une collision, validez l’ensemble complet des frères demandé et effectuez toutes les mises à jour avec un seul client avant de valider la transaction. Cette combinaison permet de réordonner les racines et les enfants tout en conservant une position finale unique pour chaque frère.

Sources