PostgreSQL kan ON CONFLICT DO UPDATE niet gebruiken als een algemene bewerking die op een van beide unieke sleutels matcht. Kies een bepalende sleutel of controleer beide sleutels expliciet binnen een transactie en wijs invoer af die naar twee verschillende rijen verwijst.

Deze tutorial helpt een ontwikkelaar die in Terminal op een Mac werkt om dat tweede beleid met node-postgres te implementeren: invoegen als geen van beide sleutels matcht, bijwerken als precies één artikel matcht en een expliciet conflict retourneren als de sleutels naar verschillende artikelen verwijzen. Alle rijen zijn synthetisch.

De SQL-voorbeelden zijn bedoeld voor PostgreSQL 18, de versie die in de geraadpleegde actuele documentatie wordt beschreven. De implementatie gebruikt een gewone, niet-gepartitioneerde tabel met twee directe unieke beperkingen. Controleer de hoofdversie van je server voordat je voorbeelden uit de handleiding van een andere versie overneemt. PostgreSQL 18 INSERT-documentatie

Waarom twee unieke beperkingen geen enkel conflictdoel kunnen vormen

Stel dat een artikel een externe verwijzing en een slug heeft. Beide moeten afzonderlijk uniek zijn. Begin met deze volledige configuratie in een lege demonstratiedatabase; sla die op als setup.sql.

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

Deze declaraties leggen twee afzonderlijke regels op. Ze vervangen door UNIQUE (external_key, slug) zou het model veranderen: alleen de combinatie hoeft dan uniek te zijn, waardoor externe sleutels en slugs in verschillende combinaties herhaald mogen worden. Door beide kolommen ook NOT NULL te houden, voorkom je dat dit voorbeeld te maken krijgt met gedrag van nullable sleutels. PostgreSQL-beperkingen

Voor DO UPDATE vereist PostgreSQL een conflictdoel. Een kolomlijst beschrijft de kolommen van een overeenkomende unieke index; het betekent niet “een van beide kolommen”. Daarom kan ON CONFLICT (external_key, slug) geen arbiter afleiden uit de twee afzonderlijke beperkingen hierboven. Als je een beperking noemt, selecteer je die beperking, en de syntaxis biedt één ON CONFLICT-clausule in plaats van een reeks alternatieve afhandelaars. PostgreSQL INSERT: ON CONFLICT-clausule

Het grotere probleem is bepalen welk bestaand artikel de binnenkomende rij voorstelt. Neem de binnenkomende waarden feed-101 en green-notes: de externe sleutel hoort bij het blauwe artikel, terwijl de slug bij het groene artikel hoort. Als je een van beide rijen bijwerkt met beide binnenkomende sleutels, ontstaat er een conflict met de andere rij. De eerste match kiezen zou een onopgeloste invoerbeslissing verhullen.

Bepaal wat er met elke combinatie van matches moet gebeuren

Gebruik de volgende beslissingstabel voordat je de query schrijft. Dit is een voorgesteld applicatiebeleid op basis van dit schema, geen gedrag dat PostgreSQL automatisch biedt.

Binnenkomende externe sleutel Binnenkomende slug Verschillende overeenkomende rijen Actie in deze tutorial
Ongebruikt Ongebruikt 0 Een artikel invoegen
Matcht een artikel Ongebruikt 1 Dat artikel bijwerken, inclusief de slug
Ongebruikt Matcht een artikel 1 Dat artikel bijwerken, inclusief de externe sleutel
Matcht artikel A Matcht artikel A 1 Artikel A bijwerken
Matcht artikel A Matcht artikel B 2 De invoer afwijzen; beide artikelen behouden

In de gevallen met één match mag elke sleutel bewust een artikel identificeren en mag de andere sleutel wijzigen. Controleer of dit het gedrag is dat je import nodig heeft. Als een externe sleutel nooit mag veranderen, moet een match op alleen de slug in plaats daarvan een applicatieconflict opleveren.

Als je werkelijke regel eenvoudiger is — “de externe sleutel bepaalt altijd het artikel” — gebruik dan een normale gerichte upsert. Deze volledige instructie kan worden uitgevoerd na de bovenstaande configuratie:

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

Hier levert EXCLUDED de voorgestelde waarden. De andere unieke beperking blijft van toepassing: als je de voorgestelde slug wijzigt in green-notes, ontstaat er een onafhankelijk uniekheidsconflict in plaats van dat wordt overgeschakeld naar het andere artikel. De garantie van een gerichte upsert voor atomair invoegen of bijwerken geldt alleen zolang er geen onafhankelijke fouten optreden. PostgreSQL INSERT-documentatie

Houd de beslissing voor beide sleutels stabiel tijdens gelijktijdige schrijfbewerkingen

Een zoekactie gevolgd door een invoeging laat ruimte tussen de beslissing en het schrijven. Een andere transactie kan in die tussenruimte een overeenkomende sleutel invoegen. Met FOR UPDATE aan de zoekactie worden opgehaalde rijen vergrendeld; als er geen rij matcht, is er geen opgehaalde rij om te vergrendelen. Daarom volstaat alleen rijvergrendeling niet voor dit specifieke pad met nul matches. PostgreSQL expliciete vergrendeling

Voor een kleine import of een incidentele bewerking vanaf je desktop is een eenvoudige oplossing om een tabelvergrendeling van het type SHARE ROW EXCLUSIVE te nemen voordat je een van beide sleutels controleert. Deze modus conflicteert met de ROW EXCLUSIVE-vergrendeling die gewone invoegingen, updates en verwijderingen verkrijgen, en ook met een andere vergrendeling van dezelfde modus. Gewone leesbewerkingen blijven ermee verenigbaar. PostgreSQL-tabelvergrendelingsmodi

De resulterende volgorde is: begin de transactie, verkrijg de tabelvergrendeling, zoek verschillende overeenkomende rijen, pas de beslissingstabel toe en commit. De expliciete vergrendeling wacht op onverenigbare vergrendelingen en blijft behouden tot de transactie eindigt. PostgreSQL LOCK

Gebruik hier expliciet READ COMMITTED. De zoekactie ziet gegevens die zijn gecommit voordat die zoekactie begint, inclusief schrijfbewerkingen die zijn voltooid terwijl het verkrijgen van de vergrendeling wachtte. In combinatie met de verkregen tabelvergrendeling biedt dit snapshotgedrag de implementatie een stabiele basis om de beslissing te nemen en de wijziging uit te voeren. PostgreSQL-transactie-isolatie

Dit is een brede vergrendeling: ook niet-gerelateerde schrijfbewerkingen aan artikelen moeten wachten. Houd de transactie kort en voer geen interactieve prompts of externe bewerkingen uit tussen het vergrendelen en het committen. Overweeg bij frequente gelijktijdige imports eerst of één canonieke sleutel de identiteit kan bepalen voordat je dit ontwerp overneemt.

Implementeer de transactie met node-postgres

Je hebt een actieve PostgreSQL 18-demonstratiedatabase nodig, de opdracht psql en een Node.js-versie die wordt ondersteund door het geïnstalleerde node-postgres-pakket. Installeer het pakket in Terminal en laad het configuratiebestand. De eerste opdracht maakt pakketmetadata aan als die nog niet in je demonstratiemap staan. Installatie en compatibiliteit van node-postgres

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

Bij deze opdrachten wordt ervan uitgegaan dat je gebruikelijke lokale verbindingsinstellingen de demonstratiedatabase al selecteren. Het programma gebruikt new Pool(), dat dezelfde PostgreSQL-verbindingsomgevingsvariabelen leest als psql; stel zo nodig PGDATABASE in op de naam van je demonstratiedatabase. Verbinding maken met node-postgres

Sla dit volledige programma op als upsert-articles.mjs. Het gaat ervan uit dat de configuratietabel geen extra triggers of regels heeft die het match- of updategedrag veranderen. Waarden worden als positionele parameters afzonderlijk van de SQL-tekst aangeleverd. Queries met node-postgres

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

Elke transactie-instructie gebruikt dezelfde uitgecheckte client, zoals node-postgres vereist. Als je deze instructies via afzonderlijke aanroepen van pool.query() verstuurt, blijft die verbindingsvereiste niet behouden. Transacties met node-postgres

Het blok finally geeft de verkregen client altijd vrij. Als het terugdraaien zelf mislukt, verwijdert release(true) de verbinding uit de pool. De buitenste pool.end() sluit de pool wanneer het desktopscript klaar is. node-postgres Pool API

Voer het programma uit met node upsert-articles.mjs. Het verwachte resultaat is het blauwe artikel met de herziene titel en ongewijzigde sleutels. De JavaScript-syntaxis is gecontroleerd met Node.js; de databasevoorbeelden zijn aan de documentatie getoetst, maar hier niet op een PostgreSQL-server uitgevoerd.

Controleer elke tak en handel fouten expliciet af

Wijzig met de configuratierijen het object dat aan upsertArticle() wordt doorgegeven om de volgende gevallen uit te proberen. Voer elk geval uit met de oorspronkelijke configuratiestatus, of houd rekening met wijzigingen uit eerdere gevallen.

externalKey slug Verwacht resultaat
feed-303 amber-notes Een nieuw artikel invoegen
feed-101 blue-revised Het blauwe artikel bijwerken en de slug vervangen
feed-303 blue-notes Het blauwe artikel bijwerken en de externe sleutel vervangen
feed-101 blue-notes De titel van het blauwe artikel bijwerken
feed-101 green-notes ARTICLE_KEY_CONFLICT genereren; geen van beide artikelen wijzigen

De zoekactie retourneert rijen, zodat een artikel dat aan beide voorwaarden voldoet slechts eenmaal wordt geteld. Met deze twee afzonderlijke niet-null unieke sleutels kunnen er hoogstens twee verschillende rijen matchen. De code lost een resultaat met twee rijen nooit op met LIMIT 1, het verwijderen van een artikel of het stilzwijgend overslaan van de invoer.

ARTICLE_KEY_CONFLICT is een door de applicatie gedefinieerde code. PostgreSQLs 23505 betekent unique_violation; het is een afzonderlijke voorwaarde. Als die laatste optreedt nadat je het voorbeeld hebt aangepast, controleer dan welke beperking is geschonden en of er tabelgedrag is toegevoegd. Behandel niet elke duplicaat als hetzelfde resultaat. PostgreSQL raadt aan foutcodes te controleren in plaats van berichttekst te parseren. PostgreSQL-foutcodes

Het verkrijgen van de vergrendeling kan wachttijd opleveren. Als je voor deze desktoptaak direct een fout wilt bij uitblijven van de vergrendeling, vervang de vergrendelingsinstructie dan door LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT. PostgreSQL genereert vervolgens een fout als de vergrendeling niet onmiddellijk kan worden verkregen, waarna het programma het terugdraaipad volgt. PostgreSQL LOCK

Maken DO NOTHING of MERGE de taak eenvoudiger?

Doelloze ON CONFLICT DO NOTHING kan een invoeging overslaan die conflicteert met een bruikbare unieke beperking of index. Kies dit als overslaan het gewenste resultaat is; hiermee implementeer je niet het updatebeleid dat hier is ontwikkeld. PostgreSQL INSERT-documentatie

MERGE is geïntroduceerd in PostgreSQL 15. Hoewel hiermee een joinvoorwaarde is toegestaan die een van beide sleutels gebruikt, moet die join nog steeds een beleid hanteren voor een bronrij die overeenkomt met twee verschillende doelartikelen. De acties blijven onderworpen aan gewone beperkingen, en het gedrag bij gelijktijdige bewerkingen is niet uitwisselbaar met dat van een gerichte upsert. Releaseopmerkingen van PostgreSQL 15, PostgreSQL MERGE

Maak voor de taak met beide sleutels het aantal matches de expliciete beslissing: nul voegt in, één werkt bij en twee leidt tot een duidelijke conflictmelding. Behoud de unieke beperkingen en kies een strategie voor gelijktijdige bewerkingen die deze beslissing stabiel houdt tot de wijziging is voltooid. De implementatie met de tabelvergrendeling biedt een concreet vertrekpunt als het acceptabel is om schrijfbewerkingen aan artikelen te serialiseren.

Bronnen