Gebruik in PostgreSQL 15 of hoger een constraint UNIQUE NULLS NOT DISTINCT die als DEFERRABLE is gedeclareerd en stel deze vervolgens uit binnen de transactie voor het herschikken. Zo kunnen artikelrijen van positie wisselen terwijl de uiteindelijke volgorde uniek blijft, ook voor rijen waarvan de ouder NULL is. Syntaxis van PostgreSQL-constraints

Deze handleiding is bedoeld voor een ontwikkelaar die in Mac Terminal werkt en synthetische artikelrijen binnen één oudergroep opnieuw moet ordenen. De handleiding bevat een SQL-demonstratie, een volledig node-postgres-programma dat de gevraagde volgorde valideert, en een alternatief voor PostgreSQL 14. Artikelen aan een andere ouder koppelen valt buiten het bereik van dit voorbeeld.

Waarom maakt een nullable ouder het uniek maken van posities ingewikkelder?

Stel dat elk artikel een id, een nullable parent_id en een positief geheel getal position heeft. Artikelen met dezelfde ouder vormen één geordende groep van broers en zussen. In dit model behoren alle artikelen zonder ouder tot één enkele hoofdgroep.

Een gewone UNIQUE (parent_id, position) dwingt die regel voor de hoofdgroep niet af. PostgreSQL behandelt nullen normaal gesproken als verschillend voor uniciteitscontrole, zodat twee hoofdartikelen allebei positie 1 kunnen innemen. Met NULLS NOT DISTINCT worden die null-waarden voor de samengestelde sleutel als gelijk behandeld. Verschillende niet-null-ouders kunnen nog steeds elk hun eigen positie 1 hebben. PostgreSQL: unieke constraints

Opnieuw ordenen brengt een apart timingprobleem met zich mee. Als artikel 1 positie 1 inneemt en artikel 2 positie 2, dan zorgt het verplaatsen van artikel 2 naar positie 1 er tijdelijk voor dat de positie van artikel 1 dubbel voorkomt. Een niet-uitstelbare unieke constraint controleert elke ingevoegde of gewijzigde rij onmiddellijk. Een uitstelbare constraint kan wachten tot het einde van de instructie, of tot de transactie is afgerond wanneer deze expliciet is uitgesteld. PostgreSQL: CREATE TABLE

Bij het opnieuw ordenen met meerdere updates zijn beide eigenschappen van belang: uniciteitscontrole die rekening houdt met nullen bepaalt de juiste groepen, en uitgestelde controle staat tijdelijke conflicten tussen updates toe.

Welke definitie moet je gebruiken voor jouw PostgreSQL-versie?

PostgreSQL 15 introduceerde NULLS NOT DISTINCT voor unieke constraints en indexen. De hoofdvoorbeelden hieronder vereisen serverversie 15 of hoger; voor PostgreSQL 14 is verderop een alternatief te zien. Controleer de versie van de verbonden server in plaats van die af te leiden uit de geïnstalleerde opdrachtregelclient. Releaseopmerkingen van PostgreSQL 15

Situatie Ondersteunde aanpak
PostgreSQL 15 of hoger, nieuwe tabel Een benoemde constraint UNIQUE NULLS NOT DISTINCT met DEFERRABLE INITIALLY IMMEDIATE
Bestaande gewone unieke constraint Vervang de definitie; met ALTER CONSTRAINT kun je de uitstelbaarheid van een unieke constraint niet wijzigen
Bestaande expressie- of gedeeltelijke unieke index Vervang de afdwingingsmethode; deze kan niet als gewone unieke constraint worden gekoppeld
PostgreSQL 14 Gebruik gegenereerde groeperingskolommen met een uitstelbare unieke constraint

De beperkingen voor bestaande objecten staan beschreven bij ALTER TABLE: op deze manier constraint-attributen wijzigen is beperkt tot foreign keys, en een index die als unieke constraint is gekoppeld, mag geen expressies bevatten en niet gedeeltelijk zijn. Een zelfstandige CREATE UNIQUE INDEX heeft ook geen clausule DEFERRABLE. PostgreSQL: ALTER TABLE, PostgreSQL: CREATE INDEX

Hoe wissel je twee hoofdartikelen om in SQL?

Vereisten zijn een bestaande lokale PostgreSQL-database van versie 15 of hoger en een werkende verbinding via psql. Open Mac Terminal en start de client met je geconfigureerde verbindingsinstellingen. De volgende opdracht slaat opstartaanpassingen over en stopt de scriptverwerking bij een fout. PostgreSQL: psql

psql -X -v ON_ERROR_STOP=1

Plak de volledige SQL-demonstratie hieronder in die sessie. Deze maakt een tijdelijke tabel aan die alleen synthetische artikelen bevat; tijdelijke tabellen verdwijnen zodra de sessie eindigt. De tabelnaam moet in die sessie nog niet in gebruik zijn. PostgreSQL: tijdelijke tabellen

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;

Na de eerste update bevat de transactie tijdelijk twee hoofdartikelen op positie 1. Na de tweede update zijn hun posities weer uniek. Als je de constraint terugzet op IMMEDIATE, worden openstaande wijzigingen op dat moment gecontroleerd. Als er nog een duplicaat is, mislukt die opdracht; anders kan de transactie worden vastgelegd. Uitstel verandert het moment waarop validatie plaatsvindt. PostgreSQL: SET CONSTRAINTS

De verwachte uiteindelijke volgorde van hoofdartikelen is:

Positie Artikel-ID Titel
1 2 Artikel B
2 1 Artikel A
3 3 Artikel C

De posities van de artikelen met een ouder blijven gelijk, omdat geen van beide updates deze selecteert. Als er in een interactieve transactie een fout optreedt, voer dan ROLLBACK; uit voordat je het opnieuw probeert. Een transactie groepeert de verwante instructies tot COMMIT of ROLLBACK. PostgreSQL: BEGIN

Hoe orden je een volledige groep van broers en zussen opnieuw met node-postgres?

Gebruik één geleende client voor de hele transactie. node-postgres vereist dit expliciet: afzonderlijke aanroepen van pool.query() zijn ongeschikt voor een transactie, omdat ze op verschillende verbindingen kunnen worden uitgevoerd. node-postgres: transacties

Het programma hieronder accepteert een volledige, geordende lijst met ID’s van broers en zussen. De aanvullende beslisregel is eenvoudig: de opgegeven lijst moet een permutatie zijn van de huidige verzameling broers en zussen. Door duplicaten, ontbrekende ID’s en ID’s uit andere groepen af te wijzen, voorkom je dat een gedeeltelijke herschikking ten onrechte slaagt.

Installeer in een lokaal Node.js-project pg, sla het volgende volledige programma op als reorder.cjs en voer het uit. Het programma gaat ervan uit dat je bestaande lokale verbindingsinstellingen beschikbaar zijn via PostgreSQL-omgevingsvariabelen. node-postgres ondersteunt dezelfde omgevingsvariabelen voor verbindingen als psql. node-postgres: verbinding maken

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 zorgt ervoor dat het ouderfilter werkt voor zowel null- als niet-nullwaarden. Gewone gelijkheid met een null-parameter selecteert de hoofdartikelen niet. Als je in plaats daarvan de voorbeeldartikelen met een ouder opnieuw wilt ordenen, wijzig dan parentId in 1 en orderedIds in [5, 4]. PostgreSQL: vergelijkingsfuncties en -operatoren

De tijdelijke aanduidingen van de vorm $1 geven waarden apart van de SQL-tekst door. De expliciete cast naar een geheel getal geeft ook het bedoelde type aan wanneer de ouderparameter null is. node-postgres: query’s

Waarom moet je de vergrendeling verkrijgen voordat je de broers en zussen leest?

De tijdelijke demonstratie heeft geen concurrerende sessies. De tabelvergrendeling laat een bewust brede aanpak voor een kleine gedeelde tabel zien: SHARE ROW EXCLUSIVE conflicteert met normale schrijftoegang tot de tabel en met zichzelf, terwijl gewone leesbewerkingen zijn toegestaan. Door de vergrendeling gedurende de transactie vast te houden, blijft de samenstelling van de groep broers en zussen stabiel tijdens validatie en updates. Ook worden schrijfbewerkingen naar andere oudergroepen geblokkeerd. Beoordeel die afweging voordat je dit op een drukke tabel gebruikt. PostgreSQL: expliciete vergrendeling

Het programma selecteert expliciet READ COMMITTED. Onder dit isolatieniveau ziet de query voor broers en zussen de vastgelegde wijzigingen tot het moment waarop die query start, inclusief wijzigingen die zijn vastgelegd voordat een voorafgaande wachtperiode op een vergrendeling eindigt. PostgreSQL: BEGIN, PostgreSQL: transactie-isolatie

De JavaScript-validatie is gecontroleerd met een geldige permutatie en met dubbele, ontbrekende en ID’s uit andere groepen. Deze deterministische controles zijn geslaagd. De databasevoorbeelden zijn vergeleken met de documentatie, maar zijn niet uitgevoerd op PostgreSQL in de onderzoeksomgeving.

Wat is het ondersteunde alternatief voor PostgreSQL 14?

Materialiseer op PostgreSQL 14 de groeperingsexpressie in gegenereerde kolommen en pas vervolgens een uitstelbare unieke constraint toe op die kolommen. Opgeslagen gegenereerde kolommen worden berekend wanneer rijen worden geschreven en kunnen niet met gewone waarden worden overschreven. PostgreSQL 14: gegenereerde kolommen

Dit zelfstandige SQL-voorbeeld gebruikt twee gegenereerde waarden om de oudergroep weer te geven. De booleanwaarde maakt onderscheid tussen hoofdartikelen en een daadwerkelijke ouder-ID 0, zodat de terugvalwaarde COALESCE geen gereserveerde ID vereist.

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;

Voor een hoofdartikel zijn de gegenereerde groeperingswaarden (true, 0); voor ouder 0 zijn ze (false, 0). Elke niet-null-ouder behoudt zijn eigen gehele waarde. De constraint geeft zo de bedoelde groepering weer zonder afhankelijk te zijn van de manier waarop PostgreSQL 15 nullen behandelt. PostgreSQL 14 ondersteunt uitstelbare unieke constraints op benoemde kolommen. PostgreSQL 14: CREATE TABLE

Wat moet je controleren voordat je een bestaande tabel aanpast?

Een expressie-index op COALESCE(parent_id, 0) kan uniciteit op basis van expressies afdwingen, maar kan niet zomaar uitstelbaar worden gemaakt of als gewone unieke constraint worden gekoppeld. Voor een gedeeltelijke index voor hoofdartikelen geldt dezelfde beperking. Als een van beide onmiddellijke afdwingingsobjecten blijft bestaan, kan die na toevoeging van een uitstelbare constraint nog steeds een wisseling verhinderen. PostgreSQL: indexen op expressies, PostgreSQL: ALTER TABLE

Controleer voordat je een bestaande definitie vervangt of er dubbele ouder-positieparen zijn, vooral bij hoofdartikelen die door gewone nullable uniciteitscontrole eerder waren toegestaan. Bij het toevoegen van een unieke constraint worden bestaande rijen gevalideerd; NOT VALID is niet beschikbaar voor unieke constraints. Los dubbelzinnige posities zorgvuldig op voordat je de wijziging probeert. PostgreSQL: ALTER TABLE

Houd de garantie van de constraint gescheiden van de ordeningsregel in de applicatie. Uniciteit staat tussenliggende posities toe, zoals 1, 4 en 9. Het programma dat de volledige lijst verwerkt, wijst bewust opeenvolgende posities toe, beginnend bij 1. De controle op groepslidmaatschap zorgt ervoor dat elke broer of zus wordt meegenomen.

Kies voor deze taak een constraint die de groepen met nullable ouders weergeeft, stel deze uit voordat de eerste conflicterende update wordt uitgevoerd, valideer de volledige opgegeven groep van broers en zussen en voer alle updates op één client uit voordat je de transactie vastlegt. Zo kun je hoofdgroepen en groepen met een ouder opnieuw ordenen, terwijl de uiteindelijke positie van elke broer of zus uniek blijft.

Bronnen