PostgreSQL INSERT ON CONFLICT: Correct Upsert with Unique Targets, DO NOTHING vs DO UPDATE

Identify the actual uniqueness rule

An ON CONFLICT clause resolves violations of arbiter constraints or indexes. These are unique constraints (PRIMARY KEY, UNIQUE) or unique indexes. The documentation states that for ON CONFLICT DO UPDATE, a conflict_target must be provided. This target specifies which conflicts trigger the alternative action by choosing arbiter indexes. The rule is enforced at the database level, not by application logic. For example, a table inventory(sku text PRIMARY KEY, qty integer NOT NULL) has a primary key constraint on sku. This constraint is the arbiter; any insert of a duplicate sku value is a conflict.

Choose the conflict target

The conflict target can use unique index inference, naming columns/expressions, or name a constraint directly with ON CONFLICT ON CONSTRAINT. Inference is often preferable. For the inventory table, the primary key on sku is inferred by ON CONFLICT (sku). The documentation notes that inference chooses all unique indexes that contain exactly the specified columns/expressions. If you name a constraint directly, it uses the index associated with that constraint. For partial unique indexes, you must include a WHERE clause in the conflict target to match the index predicate.

Use DO NOTHING deliberately

ON CONFLICT DO NOTHING silently discards rows that would cause a conflict with any arbiter constraint or index. The conflict target is optional; if omitted, conflicts with all usable unique constraints are handled. Use this when you want to insert only new rows and ignore duplicates. For example, INSERT INTO inventory (sku, qty) VALUES ('A', 3) ON CONFLICT DO NOTHING; does nothing if SKU 'A' already exists. The statement succeeds, and the count returned indicates the number of rows actually inserted (zero in this case).

Update from excluded values

ON CONFLICT DO UPDATE modifies the existing conflicting row. Within the SET clause, the special EXCLUDED alias provides the values originally proposed for insertion. The documentation specifies that when referencing a column, do not include the table's name. For the inventory example, INSERT INTO inventory (sku, qty) VALUES ('A', 3) ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty; replaces the existing quantity with 3. It does not add the values. To perform an additive update, you must explicitly reference the existing row: SET qty = inventory.qty + EXCLUDED.qty.

Trace a two-row example

Consider inserting two rows where one conflicts and one does not. With inventory initially containing ('A', 2), execute: INSERT INTO inventory (sku, qty) VALUES ('A', 3), ('B', 5) ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;. The row for SKU 'A' conflicts, triggering an update that sets its qty to 3. The row for SKU 'B' does not conflict and is inserted. The command returns INSERT 0 2, indicating two rows were processed (one updated, one inserted). The final state is ('A', 3), ('B', 5).

Handle duplicate rows in one statement

PostgreSQL documents ON CONFLICT DO UPDATE as deterministic: one command cannot affect the same existing row more than once. With repeated proposed keys, such as VALUES ('A', 3), ('A', 4), a cardinality violation can occur. This is not accurately described as an ordinary duplicate-key error occurring before ON CONFLICT gets a chance to act. Do not rely on the first or last proposed value winning.

Deduplicate the input or aggregate repeated keys according to an explicit business rule before submitting the statement. For replacement quantities, decide which observation should win; for additive quantities, decide whether summing is appropriate. On a statement error, its changes do not become a partially successful upsert. In an explicit transaction, recover using the application's rollback or savepoint policy.

Understand concurrent behavior

ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome for each row, even under high concurrency. However, the documentation warns that while CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is running on a unique index, INSERT ... ON CONFLICT on the same table may unexpectedly fail with a unique violation. The statement takes a lock on the conflicting row. If two concurrent transactions attempt to insert the same key, one will succeed with an insert, and the other will conflict and take the DO UPDATE action on the now-existing row.

Check limits beyond the statement

Atomic upsert does not guarantee business-level exactly-once processing. Retrying an additive update can increment a quantity again unless the application deduplicates the logical operation. Other constraints, permissions and triggers can still reject the statement. By default, nullable unique columns treat nulls as distinct, permitting multiple nulls unless NULLS NOT DISTINCT is specified. Partial unique indexes apply only to their predicate; conflict-target inference must select an appropriate arbiter.