PostgreSQL cannot use ON CONFLICT DO UPDATE as a general “match either unique key” operation. Choose a defining key, or explicitly check both keys in a transaction and reject inputs that identify two different rows.

This tutorial helps a developer working in Terminal on a Mac implement that second policy with node-postgres: insert when neither key matches, update when exactly one article matches, and return an explicit conflict when the keys point to different articles. All rows are synthetic.

The SQL examples target PostgreSQL 18, the version served by the retrieved current documentation. The implementation uses an ordinary, nonpartitioned table with two immediate unique constraints. Check your server’s major version before adapting examples from another version’s manual. PostgreSQL 18 INSERT documentation

Why two unique constraints cannot become one conflict target

Suppose an article has an external reference and a slug. Each must be unique independently. Start with this complete setup in an empty demonstration database; save it as 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');

These declarations enforce two separate rules. Replacing them with UNIQUE (external_key, slug) would change the model: only the pair would need to be unique, allowing repeated external keys and repeated slugs in different combinations. Keeping both columns NOT NULL also avoids introducing nullable-key behavior into this example. PostgreSQL Constraints

For DO UPDATE, PostgreSQL requires a conflict target. A column list describes the columns of a matching unique index; it does not mean “either column.” Consequently, ON CONFLICT (external_key, slug) cannot infer an arbiter from the two separate constraints above. Naming a constraint selects that constraint, and the syntax provides one ON CONFLICT clause rather than a chain of alternative handlers. PostgreSQL INSERT: ON CONFLICT Clause

The deeper problem is deciding which existing article the incoming row represents. Consider incoming values feed-101 and green-notes: the external key belongs to the blue article, while the slug belongs to the green article. Updating either row with both incoming keys would conflict with the other row. Choosing the first match would conceal an unresolved input decision.

Decide what every combination of matches should do

Use the following decision table before writing the query. It is a proposed application policy derived from this schema, rather than behavior PostgreSQL automatically supplies.

Incoming external key Incoming slug Distinct matching rows Action in this tutorial
Unused Unused 0 Insert an article
Matches an article Unused 1 Update that article, including its slug
Unused Matches an article 1 Update that article, including its external key
Matches article A Matches article A 1 Update article A
Matches article A Matches article B 2 Reject the input; preserve both articles

The one-match cases deliberately allow either key to identify an article and allow the other key to change. Confirm that this is the behavior your import needs. If an external key must never change, the slug-only match should instead become an application conflict.

If your actual rule is simpler—“the external key always determines the article”—use a normal targeted upsert. This complete statement runs after the setup above:

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;

Here EXCLUDED supplies the proposed values. The other unique constraint still applies: changing the proposed slug to green-notes produces an independent uniqueness conflict rather than switching to the other article. Targeted upsert’s atomic insert-or-update guarantee is conditional on the absence of independent errors. PostgreSQL INSERT documentation

Make the either-key decision stable during concurrent writes

A lookup followed by an insert creates a gap between deciding and writing. Another transaction could insert a matching key in that gap. Adding FOR UPDATE to the lookup locks retrieved rows; when no row matches, there is no retrieved row to lock. That makes row locking alone insufficient for this particular zero-match path. PostgreSQL Explicit Locking

For a small import or occasional desktop operation, a straightforward solution is to take a SHARE ROW EXCLUSIVE table lock before checking either key. This mode conflicts with the ROW EXCLUSIVE lock acquired by ordinary inserts, updates and deletes, and it conflicts with another instance of itself. Ordinary reads remain compatible. PostgreSQL table-level lock modes

The resulting sequence is: begin the transaction, acquire the table lock, find distinct matching rows, apply the decision table, and commit. The explicit lock waits for incompatible locks and remains held until the transaction ends. PostgreSQL LOCK

Use READ COMMITTED explicitly here. Its lookup sees data committed before that lookup begins, including writes completed while the lock acquisition was waiting. Combining that snapshot behavior with the acquired table lock gives this implementation a stable basis for deciding and writing. PostgreSQL Transaction Isolation

This is a broad lock: unrelated article writes also wait. Keep the transaction short, with no interactive prompts or external work between locking and committing. For frequent concurrent imports, reconsider whether one canonical key can define identity before adopting this design.

Implement the transaction with node-postgres

You need a running PostgreSQL 18 demonstration database, the psql command, and a Node.js version supported by your installed node-postgres package. In Terminal, install the package and load the setup file. The first command creates package metadata if your demonstration directory does not already have it. node-postgres installation and compatibility

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

These commands assume your normal local connection settings already select the demonstration database. The program uses new Pool(), which reads the same PostgreSQL connection environment variables used by psql; set PGDATABASE to your demonstration database name if needed. node-postgres Connecting

Save this complete program as upsert-articles.mjs. It assumes the setup table has no additional triggers or rules that change its matching or update behavior. Values use positional parameters supplied separately from the SQL text. node-postgres Queries

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();
}

Every transaction statement uses the same checked-out client, as node-postgres requires. Sending these statements through separate pool.query() calls would not preserve that connection requirement. node-postgres Transactions

The finally block always releases the acquired client. If rollback itself fails, release(true) discards the connection. The outer pool.end() closes the pool when the desktop script finishes. node-postgres Pool API

Run the program with node upsert-articles.mjs. The expected result is the blue article with its revised title and unchanged keys. The JavaScript syntax was checked with Node.js; the database examples were inspected against the documentation but were not executed against a PostgreSQL server here.

Check each branch and handle failures explicitly

Using the setup rows, change the object passed to upsertArticle() to exercise the following cases. Run each case against the original setup state, or account for changes made by earlier cases.

externalKey slug Expected outcome
feed-303 amber-notes Insert a new article
feed-101 blue-revised Update the blue article and replace its slug
feed-303 blue-notes Update the blue article and replace its external key
feed-101 blue-notes Update the blue article’s title
feed-101 green-notes Throw ARTICLE_KEY_CONFLICT; change neither article

The lookup returns rows, so an article matching both conditions is counted once. With these two independent non-null unique keys, at most two distinct rows can match. The code never resolves a two-row result by using LIMIT 1, deleting an article, or silently skipping the input.

ARTICLE_KEY_CONFLICT is an application-defined code. PostgreSQL’s 23505 means unique_violation; it is a separate condition. If the latter appears after adapting the example, inspect the violated constraint and any added table behavior rather than treating every duplicate as the same outcome. PostgreSQL recommends checking error codes instead of parsing message text. PostgreSQL Error Codes

Lock acquisition can wait. If you prefer immediate failure for this desktop task, replace the locking statement with LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT. PostgreSQL then raises an error when it cannot acquire the lock immediately, and the program follows its rollback path. PostgreSQL LOCK

Would DO NOTHING or MERGE simplify the task?

Targetless ON CONFLICT DO NOTHING can skip an insert that conflicts with any usable unique constraint or index. Choose it when skipping is the intended result; it does not implement the update policy developed here. PostgreSQL INSERT documentation

MERGE was introduced in PostgreSQL 15. Although it permits a join condition involving either key, that join still needs a policy for a source row matching two different target articles. Its actions remain subject to ordinary constraints, and its concurrent behavior is not interchangeable with targeted upsert. PostgreSQL 15 release notes, PostgreSQL MERGE

For the either-key task, make the match count the explicit decision: zero inserts, one updates, and two stops with a useful conflict. Keep the unique constraints in place and choose a concurrency strategy that preserves that decision until the write completes. The table-lock implementation provides a concrete starting point when serializing article writes is acceptable.

Sources