PostgreSQL Upserts with INSERT ... ON CONFLICT: Atomic Insert-or-Update

The problem and the useful takeaway

Your application needs to record inventory adjustments. If the SKU already exists, the quantity should increase; if it does not exist, a new row should be created. Running a SELECT first and then choosing INSERT or UPDATE requires two round trips and leaves a gap where another session can insert the same SKU between your check and your write.

The useful takeaway is that INSERT ... ON CONFLICT makes the check and the write atomic. PostgreSQL attempts the insert, and if a unique constraint or primary key is violated, it either skips the row or updates the existing row, depending on the clause you choose. This feature is available in PostgreSQL 9.5 and later.

Requirement: a unique index or primary key

The conflict target after ON CONFLICT must reference columns covered by a unique index or primary key. If you name columns that are not uniquely constrained, PostgreSQL raises an error instead of performing an upsert. A primary key satisfies this requirement automatically.

Worked example: adjust inventory by SKU

Run the following in a psql session connected to the target database. You need INSERT and UPDATE privileges on the table, or be the table owner.

CREATE TABLE inventory (
    sku text PRIMARY KEY,
    quantity integer NOT NULL DEFAULT 0,
    updated_at timestamptz NOT NULL DEFAULT now()
);

Meaningful placeholders: replace 'ABC-123' with the SKU you are receiving, and replace 10 with the quantity to add.

INSERT INTO inventory (sku, quantity)
VALUES ('ABC-123', 10)
ON CONFLICT (sku)
DO UPDATE SET
    quantity = inventory.quantity + EXCLUDED.quantity,
    updated_at = now();

EXCLUDED is a special table reference containing the row proposed by the INSERT. If no row with SKU ABC-123 exists, PostgreSQL inserts it. If the SKU already exists, the conflict target matches the primary key, and PostgreSQL updates the existing row instead.

Expected checks: the server returns a command tag such as INSERT 0 1 when a new row is created, or UPDATE 1 when an existing row is modified. Re-query to confirm the data state:

SELECT sku, quantity, updated_at FROM inventory WHERE sku = 'ABC-123';

Relevant risk: this statement serializes concurrent upserts on the same conflicting row, but it does not protect read-modify-write logic that runs outside the statement. If your application reads the row before this INSERT runs, another session can still overwrite changes made between the read and the upsert.

DO NOTHING versus DO UPDATE

Clause	Behavior when a unique conflict occurs	Use when
DO NOTHING	The insert is skipped; no error is raised.	Duplicates should be ignored silently.
DO UPDATE SET ...	The existing row is overwritten with values from EXCLUDED.	The existing row should be refreshed or incremented.

Limits and common mistakes

• The conflict target must match a unique index. ON CONFLICT (sku) works here because sku is the primary key. Listing columns without a unique index causes an error.
• Expression-based targets need a matching expression index. ON CONFLICT (lower(sku)) requires a unique index on lower(sku); otherwise the statement fails.
• DO NOTHING silently discards the row. Use it only when skipping duplicates is intentional, or you may lose data without any error message.
• Other constraints still fail. ON CONFLICT handles unique-violation errors only. A foreign-key or check-constraint violation still aborts the statement.
• Omitting the conflict target column list is generally invalid for upserts; you must name the constrained columns.

Verification and rollback

Because INSERT ... ON CONFLICT changes data, test it inside a transaction so you can roll back:

BEGIN;
INSERT INTO inventory (sku, quantity)
VALUES ('TEST-001', 5)
ON CONFLICT (sku) DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity;
SELECT sku, quantity FROM inventory WHERE sku = 'TEST-001';
ROLLBACK;

Practical check: compare the command tag against your intent. If you expected an insert but see UPDATE 1, the row already existed. If you see INSERT 0 1, the row was created. After a ROLLBACK, confirm the test row is gone with a SELECT outside the transaction.