PostgreSQL / Basic: relational foundations / Lesson 6

Writes, RETURNING and conflict handling

Make inventory writes state their own preconditions and return their actual effects. Separate a duplicate-key update from business-level idempotency.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Use scoped INSERT, UPDATE, DELETE and RETURNING.
Explain ON CONFLICT and safe retry identity.

Put the invariant inside the write

Reading quantity into an application, subtracting three and writing an absolute new value can lose a concurrent reservation. Express the arithmetic and sufficient-stock predicate in one UPDATE. A preliminary SELECT is useful for review but does not reserve a business right by itself. Name INSERT columns explicitly and qualify the training schema. PostgreSQL RETURNING reports rows affected by the statement without a separate potentially stale read; the application must interpret an empty returned set as a meaningful outcome.

Rehearse a conditional change

Assume item 4 exists in an isolated training inventory with quantity nine. This transaction subtracts three only when quantity is at least three, returns quantity six and rolls back the rehearsal. If stock is two, no row is returned and the value remains two. A missing item also returns no row, so distinguish that case only if the API contract needs it. The example was checked by reasoning and never executed against a configured database.

sql Illustrative SQL; expected results are reasoned, not measured.

BEGIN;
UPDATE quay_lab.inventory SET quantity = quantity - 3
WHERE item_id = 4 AND quantity >= 3
RETURNING item_id, quantity;
ROLLBACK;

Expected output

Expected before rollback when initially 9: item_id = 4, quantity = 6.

A zero-row result is a failed precondition or missing target, not permission to remove the WHERE condition.

Name the conflict arbiter deliberately

INSERT ... ON CONFLICT can do nothing or update under its documented arbiter rules. DO UPDATE requires a suitable unique arbiter; exclusion constraints cannot arbitrate that update action. The UPDATE branch uses the special excluded relation for the proposed row. PostgreSQL does not use MySQL VALUES(column) syntax for this purpose. Pick the intended conflict key rather than treating every error as duplication. ON CONFLICT is useful for reference synchronization, but incrementing a balance again on every repeated request is still a duplicate business effect.

Use excluded for the proposed value

This temporary fixture is a session-local teaching table, not a service schema change to run here. The first insert creates quay Q1. The second insert conflicts on the primary key and replaces its display label through DO UPDATE, returning Q1 with East Quay. PostgreSQL ON CONFLICT was introduced in 9.5; modern supported releases provide it, but feature details still belong to the target manual. A RETURNING value describes this statement’s row, not a promise about future concurrent writes.

sql Illustrative SQL; expected results are reasoned, not measured.

CREATE TEMP TABLE quays (code text PRIMARY KEY, label text NOT NULL);
INSERT INTO quays (code, label) VALUES ('Q1', 'Old Quay');
INSERT INTO quays (code, label) VALUES ('Q1', 'East Quay')
ON CONFLICT (code) DO UPDATE SET label = excluded.label
RETURNING code, label;

Expected output

code | label
Q1   | East Quay

The conflict target is code; the proposed label comes from excluded.

Verify scope and request identity

DELETE supports WHERE and RETURNING, which can expose the exact training identifiers removed. TRUNCATE has a different locking and trigger contract and should not be substituted casually. Unlike some databases, PostgreSQL can roll back ordinary TRUNCATE in a transaction, but this does not make it appropriate for scoped business deletion. Network uncertainty around COMMIT requires request identity and reconciliation: store a unique idempotency key with the effect and recorded result. Retrying only the last statement can repeat money movement or detach related audit work.

Common errors and fixes

A reservation overwrites a concurrent balance

Why it happens: An absolute quantity was calculated from a stale read.
How to fix it: Use arithmetic UPDATE with the stock predicate and inspect RETURNING.
Prevent it next time: Keep invariants inside atomic writes or a reviewed locking transaction.

there is no unique or exclusion constraint matching ON CONFLICT (42P10)

Why it happens: The proposed conflict target has no suitable arbiter.
How to fix it: Choose the intended existing key or design a reviewed constraint migration.
Prevent it next time: Review business uniqueness before selecting upsert syntax.

A retried payment is applied twice

Why it happens: A row-level upsert was confused with event idempotency.
How to fix it: Persist a unique request key and prior result with the payment transaction.
Prevent it next time: Test lost-response retries, not only duplicate primary keys.

Check your understanding

1. Quantity is two and a request needs three. Explain the conditional UPDATE outcome and next application decision.

Show worked solution

The predicate fails, no row is returned and quantity remains two. Treat this as an unsuccessful reservation. If the API distinguishes missing items from insufficient stock, perform an appropriate follow-up within a defined consistency contract. Do not retry by weakening the predicate or silently accepting a negative balance.

2. A reference table has a unique station code. Why is DO UPDATE suitable for a label refresh but not sufficient for incrementing a retried payment?