PostgreSQL upsert: ON CONFLICT, keys, and RETURNING

A PostgreSQL upsert uses INSERT ... ON CONFLICT to insert a row or take a specified action when a unique key already exists. Choose DO NOTHING to keep the existing row, or DO UPDATE to change it. The conflict target must represent the identity you intend to match.

This guide uses a small stock table to show those decisions, including newer revisions, skipped updates, repeated keys in a batch, and retries that accidentally add a quantity twice. We ran 20 checks on PostgreSQL 18.6 in a fresh local database. The data is invented, and no production database was involved.

Define the unique key before writing the upsert

A SKU identifies an item within one tenant in this example. The same SKU may appear for another tenant, so the primary key includes both tenant_id and sku. The quantity must be nonnegative, and revisions start at 1.

Run the examples in order in a disposable database, with psql autocommit enabled. Stop if the upsert_lab schema already exists. Our PostgreSQL hosting guide covers the choice between a managed service and a database you operate yourself; this lab uses an isolated local server.

CREATE SCHEMA upsert_lab;
CREATE TABLE upsert_lab.stock (
    tenant_id integer NOT NULL,
    sku text NOT NULL,
    quantity integer NOT NULL CHECK (quantity >= 0),
    revision integer NOT NULL CHECK (revision > 0),
    PRIMARY KEY (tenant_id, sku)
);
INSERT INTO upsert_lab.stock VALUES (7, 'cable', 10, 1);

The initial cable record holds quantity 10 at revision 1. Our test also inserted the same SKU for tenant 8 successfully. That is a separate record under this key, not a duplicate of tenant 7’s cable.

The PostgreSQL constraints documentation explains composite unique keys and primary keys. Do not use an ordinary, nonunique index as a substitute for the uniqueness rule. In our test, ON CONFLICT (sku) failed because this table has no unique key on SKU alone.

Use DO NOTHING to preserve the existing record

INSERT INTO upsert_lab.stock (tenant_id, sku, quantity, revision)
VALUES (7, 'cable', 99, 2)
ON CONFLICT (tenant_id, sku) DO NOTHING
RETURNING tenant_id, sku, quantity, revision;

This statement returned no rows, and the stored quantity stayed at 10 with revision 1. The proposed quantity of 99 was discarded. The RETURNING clause did not retrieve the existing record.

Use this behavior when keeping the first stored record is the intended policy. An empty result is meaningful: it tells you this statement did not return a newly inserted row. If the application needs the current record, retrieve it deliberately and account for the transaction isolation and concurrent changes involved.

Replace values only when the incoming revision is newer

Here, revisions come from a source that assigns increasing numbers per tenant and SKU. This is an explicit assumption of the example. The database does not know whether the source’s revision is trustworthy.

INSERT INTO upsert_lab.stock AS current_stock
    (tenant_id, sku, quantity, revision)
VALUES (7, 'cable', 15, 2)
ON CONFLICT (tenant_id, sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    revision = EXCLUDED.revision
WHERE EXCLUDED.revision > current_stock.revision
RETURNING tenant_id, sku, quantity, revision;

Returned columns, shown in psql’s unaligned format:

7|cable|15|2

current_stock refers to the existing row. EXCLUDED supplies the proposed row, so EXCLUDED.quantity is 15 and EXCLUDED.revision is 2. The WHERE condition compares that revision with the stored one before allowing the update.

Our check confirmed that quantity 15 replaced quantity 10. It did not add 15 to it. Repeating the exact statement returned no row and left revision 2 unchanged. Sending the same revision with different values would also be skipped by this rule; detect and resolve that inconsistent source data rather than assuming it was accepted.

The INSERT reference describes the conflict target, the special EXCLUDED row, and the update condition. If your data has no reliable revision, choose another explicit policy instead of adding this field solely to copy the example.

Check both the stale-update and new-record paths

Try an older cable revision after the successful update:

INSERT INTO upsert_lab.stock AS current_stock
    (tenant_id, sku, quantity, revision)
VALUES (7, 'cable', 4, 1)
ON CONFLICT (tenant_id, sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    revision = EXCLUDED.revision
WHERE EXCLUDED.revision > current_stock.revision
RETURNING tenant_id, sku, quantity, revision;

It returned no rows. The stored record remained quantity 15 at revision 2. PostgreSQL’s RETURNING documentation explains returning data from modified rows; it is not a general-purpose lookup for rows the statement chose not to modify.

Now use the same conflict policy for a key that does not exist:

INSERT INTO upsert_lab.stock AS current_stock
    (tenant_id, sku, quantity, revision)
VALUES (7, 'adapter', 6, 1)
ON CONFLICT (tenant_id, sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    revision = EXCLUDED.revision
WHERE EXCLUDED.revision > current_stock.revision
RETURNING tenant_id, sku, quantity, revision;

Observed output:

7|adapter|6|1

The adapter was inserted at quantity 6, revision 1. No conflict meant there was no existing revision to compare. The update’s WHERE condition is not an insertion filter. Use constraints or validate incoming data when a rule must also apply to new rows.

Handle repeated keys before sending a batch

This next statement is intentionally invalid for our fixture. Run it separately with autocommit enabled:

INSERT INTO upsert_lab.stock AS current_stock
    (tenant_id, sku, quantity, revision)
VALUES (7, 'cable', 20, 3), (7, 'cable', 25, 4)
ON CONFLICT (tenant_id, sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    revision = EXCLUDED.revision
WHERE EXCLUDED.revision > current_stock.revision;

It failed with SQLSTATE 21000, a cardinality violation, because it attempted to update the same cable record twice in one statement. A subsequent check found quantity 15 at revision 2, confirming that the failed statement did not leave its first attempted update behind.

Reduce each incoming batch to one accepted row per conflict key before writing it. For these revisioned records, one possible policy is to retain the highest revision, provided equal revisions agree on the payload. Reject or quarantine conflicting ties. Input order alone is a poor substitute for a defined rule, and the statement above does not silently choose its final row as the winner.

If you execute a failing statement inside an explicit transaction, roll back or recover through an appropriate savepoint before continuing. This walkthrough uses separate autocommit statements so its intentional failure does not prevent later examples from running.

Distinguish replacing a value from applying a delta

A counter has different semantics from a stock snapshot. Create a separate table for this example:

CREATE TABLE upsert_lab.counters (
    counter_key text PRIMARY KEY,
    total bigint NOT NULL CHECK (total >= 0)
);

Run this statement twice:

INSERT INTO upsert_lab.counters AS c (counter_key, total)
VALUES ('imports', 3)
ON CONFLICT (counter_key) DO UPDATE
SET total = c.total + EXCLUDED.total
RETURNING counter_key, total;

The first execution returned imports|3. The second returned imports|6. Here, c.total is the stored total, and EXCLUDED.total is the incoming increment.

That behavior is correct for two distinct increments. It is wrong if the second execution is a retry of the same event. ON CONFLICT does not assign an event identity or prevent a delta from being applied twice. If retries must not double-count, design an event-deduplication step and the counter update within the same transaction. This guide does not implement that application workflow.

Keep other constraint errors visible

Our failure checks established several useful boundaries:

• A negative quantity still failed its CHECK constraint with DO NOTHING.
• A null tenant ID still failed NOT NULL.
• Targeting one unique key did not suppress a violation of a different unique key in a separate test table.
• A conflict target without a matching unique key failed before any useful upsert could occur.

We also checked nullable unique keys in temporary tables. An ordinary UNIQUE key allowed two nulls; UNIQUE NULLS NOT DISTINCT treated them as conflicting. The stock table avoids that ambiguity with its nonnull primary key. Decide what a missing business identifier means before choosing its uniqueness rule.

Account for transactions, then clean up the lab

PostgreSQL documents an atomic insert-or-update decision for ON CONFLICT DO UPDATE, subject to independent errors. Our conditional example can also skip an update when its revision test is false. Atomicity does not mean an operation cannot wait for a competing transaction, or that an entire business workflow is protected by this one statement. See the transaction isolation documentation for the concurrency rules.

Our checks were sequential, not a concurrent-writer or performance test. Before deployment, test application permissions, contention, retries, and rollback behavior with your actual workload. If you inspect a write using EXPLAIN ANALYZE, remember that it executes the statement; our PostgreSQL EXPLAIN guide covers that distinction.

Remove only the disposable tables created for this exercise:

DROP TABLE upsert_lab.counters;
DROP TABLE upsert_lab.stock;
DROP SCHEMA upsert_lab;

The test runner verified that the lab schema was gone, stopped its local PostgreSQL server, and removed its temporary password file. No cloud, trigger, partial-index, partition, or production-role behavior was tested.