Last reviewed: November 18, 2026 (UTC). Official references checked against the current PostgreSQL 18 documentation.
Direct answer
A PostgreSQL upsert review should connect four things: the database constraint that identifies a conflict, the columns and conditions allowed to change an existing row, the meaning of every returned result, and the behavior of concurrent writers. Inspect the SQL emitted by the coding agent’s ORM or driver, then test it against the candidate schema through the actual runtime role.
PostgreSQL’s INSERT reference
documents atomic conflict handling, but a conditional DO UPDATE ... WHERE can leave the conflicting row unchanged. That row is also absent from RETURNING. A successful command therefore needs an explicit application outcome such as applied or skipped; the surrounding transaction still has to commit before the application reports durable success.
Who this is for
Use this guide when a coding agent changes INSERT ... ON CONFLICT, an ORM upsert method, a unique constraint, an ingestion batch, or the retry wrapper around a database write. It is especially useful for versioned projections and synchronization jobs where an older event must never overwrite newer state.
The example below maintains one item projection per tenant and item key. Its policy is deliberate: higher source versions replace the projection; equal and older versions leave it unchanged. Other applications need their own written replacement rule.
Key takeaways
- Match the conflict target to the intended business identity and the constraints installed by the migration.
- Review the complete update assignment list and any condition on the update branch.
- Define what zero returned rows means before writing the response mapper.
- Test overlapping transactions with controlled synchronization, including commit and rollback of the first writer.
- Treat duplicate keys within one batch, permission failures, and unrelated constraint violations as separate cases.
- Keep transaction retry and uncertain-commit handling explicit; an upsert alone cannot settle an external side effect.
Sources checked
Three primary PostgreSQL references were retrieved successfully on November 18, 2026 UTC:
- INSERT
supports conflict-target inference, the
excludedrow, update conditions, required privileges,RETURNING, and the cardinality restriction on affecting one existing row repeatedly in one command. - Constraints , especially Unique Constraints and Primary Keys, supports composite identity, automatic unique indexes, and the default treatment of nulls in unique constraints.
- Transaction Isolation , especially Read Committed and Repeatable Read, supports statement snapshots, concurrent conflict behavior, and restarting an entire transaction after a serialization failure.
These maintained technical references support an evergreen review task. They identified PostgreSQL 18 as current when checked; use the documentation for the major version you deploy. The schema, application outcome names, and test plan below are review recommendations, not reports of an executed database test.
Contract details to verify
Match the conflict key to the business identity
Write down what makes two inputs refer to the same object. In this example, an item key is unique only inside its tenant. A disposable fixture can use:
CREATE TABLE item_projection (
tenant_id uuid NOT NULL,
item_key text NOT NULL,
source_version bigint NOT NULL CHECK (source_version > 0),
body text NOT NULL,
PRIMARY KEY (tenant_id, item_key)
);
The composite primary key enforces uniqueness and non-null identity. A unique constraint on item_key alone would impose a different, global identity and could make two tenants collide. Obtain the tenant identifier from trusted application authorization before executing the statement; including it in a key does not authorize a caller to choose another tenant.
Check the deployed migration alongside the query. PostgreSQL infers matching unique indexes from the conflict target’s columns or expressions and, where supplied, its predicate. Inference fails when no usable match exists. A partial-index design needs its predicate reviewed too; the INSERT reference notes that a matching non-partial unique index can also be inferred. Only supported non-deferrable arbiters qualify.
If the patch uses a nullable unique key instead, inspect null behavior explicitly. The Constraints reference
explains that default uniqueness permits duplicates containing null in a constrained column. Choose NOT NULL or a deliberate NULLS NOT DISTINCT design according to the identity contract.
Use the site’s database migration review workflow for existing-data checks, deployment ordering, and recovery when changing that key.
Make the replacement rule visible in SQL
For this projection, the source assigns monotonically increasing versions within each tenant/item identity. Bind the four values through the driver’s parameter API. This is a statement template, not a standalone command with literal values:
INSERT INTO item_projection AS current_item
(tenant_id, item_key, source_version, body)
VALUES ($1::uuid, $2::text, $3::bigint, $4::text)
ON CONFLICT (tenant_id, item_key) DO UPDATE
SET source_version = EXCLUDED.source_version,
body = EXCLUDED.body
WHERE EXCLUDED.source_version > current_item.source_version
RETURNING tenant_id, item_key, source_version;
The update branch changes only source_version and body. It preserves the identity columns and rejects equal or older replacement versions through its condition. An equal version with a different body is also skipped under this exact rule. If that indicates a producer integrity error in your application, add an explicit detection policy; this SQL does not compare bodies or report that disagreement.
The version must come from the authorized source’s ordering contract. A caller-controlled large number could otherwise prevent later legitimate events from updating the projection. Record whether versions can reset, whether several producers share the sequence, and how a source reset is handled before accepting a simple greater-than comparison.
PostgreSQL exposes the proposed row through EXCLUDED; per-row BEFORE INSERT trigger changes are reflected there. Inspect relevant triggers as part of the write path. An ORM option named “upsert” does not establish which columns, predicates, triggers, or conflict target the generated command actually uses.
Give empty results a defined meaning
For this single-input statement, one returned row means the command inserted or updated the projection. Zero returned rows means the conditional update did not apply, under the stated fixture and trigger assumptions. Map those cases explicitly, and finalize the outcome only after the transaction commits.
Do not infer insertion versus update from the fact that a row was returned: both paths can return one. Likewise, an empty result is not the existing row. Keep any authorized follow-up read and its possible missing-row outcome explicit in the application contract.
The same distinction matters for DO NOTHING. With an omitted conflict target, it handles conflicts with all usable constraints and unique indexes, which may hide a different identity mistake from the one the feature intended to tolerate. Prefer a reviewed target when suppression should be narrow; other errors still need ordinary failure handling.
The Transaction Isolation reference
explains that, at Read Committed, a concurrent transaction can cause DO NOTHING to skip insertion even when that transaction’s effects are absent from the command’s snapshot. Consequently, a fallback SELECT in the same command cannot be assumed to find the conflicting row. Define a subsequent lookup and race policy if the caller requires an existing record.
Verify runtime authority and the transaction boundary
The INSERT reference requires suitable insert and update privileges, plus select privileges for columns read by the conflict target, expressions, conditions, and RETURNING. Run tests as the effective application role. An owner-session success does not establish that the deployed runtime can execute the statement.
For protected tenant tables, also use the site’s PostgreSQL row-level security review . Its role, policy, and connection-context checks complement the conflict and result checks here.
Keep related database changes inside the intended transaction and test their commit together. Sending an email or invoking another service is a separate effect: neither the unique key nor a returned row proves that such an action happened once.
Happy-path operator workflow
- Freeze the patch revision. Inventory the emitted statement, schema migration, runtime role, isolation level, triggers, result mapper, and transaction wrapper.
- Recreate the prior schema in a disposable database with the deployment’s PostgreSQL major version. Apply the candidate migration and seed synthetic tenants A and B with known item keys and versions.
- Run the actual application path for a new identity, a higher version, an equal version, and an older version. Assert returned-row cardinality, final version, final body, and transaction outcome.
- Exercise the same item key in tenant B. Verify that both tenant-scoped projections can exist and that authorization still constrains the caller.
- Run overlapping writers through separate connections. Coordinate their progress with harness barriers and database observations; give the test a bounded deadline.
- Attach the assertions, generated SQL, schema identity, and sanitized outcomes to the revision under review.
For the concurrency check, begin with the chosen identity absent. Use explicit Read Committed transactions and no extra triggers in the minimal fixture. The intended schedule is:
| Stage | Session A | Session B | Expected evidence |
|---|---|---|---|
| First write | Insert version 1 through the template; keep the transaction open. | Wait at the harness barrier. | A has a provisional returned row. |
| Overlap | Remain open. | Submit version 2 for the same identity. | B has not completed while A’s conflicting write remains unresolved. |
| Resolve | Commit. | Finish the statement, then commit. | B returns version 2; the final committed projection has one row at version 2. |
Repeat with A writing version 2 and B submitting version 1. After A commits, B should skip the replacement and return no row. Then repeat the absent-identity case with A rolling back: B should be able to insert its version. Treat timeouts and unrelated errors as failed or inconclusive test runs requiring investigation, rather than counting them as the expected skip.
The INSERT reference says the update condition is evaluated after a conflict has been identified and the conflicting row is locked. A false condition therefore does not make contention disappear. Keep application transactions short enough for their agreed latency budget and verify behavior under the actual workload.
Error-path operator workflow
Classify the failure before choosing a retry. A missing matching arbiter points to schema/query disagreement. A check, permission, or unrelated unique violation requires its own diagnosis. Preserve the error class, roll back the failed transaction, repair the responsible contract, and rerun from a clean fixture.
For a batch, validate repeated business identities before constructing the command. PostgreSQL forbids an ON CONFLICT DO UPDATE command from affecting the same existing row more than once and can raise a cardinality violation. Choose an explicit batch policy, such as rejecting repeated keys or selecting one input by a documented ordering rule. Silently keeping whichever record happens to come last makes the result depend on incidental input order.
At isolation levels that produce a serialization failure, PostgreSQL documents aborting and restarting the whole transaction. A retry wrapper should rebuild its reads and decisions inside that new transaction, use a bounded attempt policy, and keep external effects outside the replayable body. Retrying only the final statement can reuse decisions from an invalid transaction snapshot.
If the connection fails around commit and its outcome is unknown, preserve the operation identity and reconcile durable application state before deciding what to repeat. Report uncertainty through the application’s recovery path. A generic catch-and-retry loop cannot distinguish a rejected transaction from a committed write whose response was lost.
Failure modes
| Review gap | Check that exposes it |
|---|---|
| The conflict target omits tenant identity. | Two tenants use the same item key; neither projection overwrites the other. |
| A nullable unique column is assumed to prevent every duplicate. | Fixtures cover the permitted null cases and the chosen constraint semantics. |
| The ORM replaces extra columns on conflict. | Inspect generated assignments and assert identity and protected fields remain unchanged. |
| Equal or older inputs are reported as applied. | Assert zero returned rows, unchanged state, and the application’s skip outcome. |
| Two inputs in one batch target the same existing row. | Verify the explicit batch rejection or reduction policy and database failure cleanup. |
| A denied runtime write is retried with an owner credential. | Permission tests assert controlled failure with no authority change. |
| An application reports success before commit. | A controlled transaction failure produces no durable-success response. |
| A serial test is treated as concurrency evidence. | Run the commit, rollback, and stale-version schedules with overlapping connections. |
FAQ
Does ON CONFLICT make a preliminary existence check safe?
A preliminary SELECT is a snapshot observation; another writer can act afterward. Put conflict arbitration in the database command backed by the intended unique identity. Retain preflight reads only for a defined application purpose, and test the actual write outcome independently.
Is MERGE interchangeable with an upsert?
Review a substitution separately. PostgreSQL’s Transaction Isolation documentation explains that MERGE with insert and update branches does not provide the same guarantee that an insert or update will occur. Concurrent inserts can lead to a uniqueness error. Preserve the required behavior when an agent changes command families.
Is an upsert an exactly-once processing guarantee?
It enforces the chosen database conflict behavior. Your version rule, transaction boundaries, retries, result delivery, and external actions still determine processing behavior. Prove each effect that the application promises rather than extending a single-row database guarantee to the whole workflow.
Reader next step
Choose one agent-written upsert and write its identity, replacement, result, and transaction contract beside the emitted SQL. Run the new, equal, stale, cross-tenant, and overlapping-writer cases against a disposable database through the runtime role. Approve the revision only when the stored state and application outcome agree in every case.