PostgreSQL UPSERT commonly refers to INSERT with an ON CONFLICT action. It can insert a row or handle a supported uniqueness conflict through a defined alternative, such as updating the existing row. The useful behavior depends on the chosen key and update rule; it does not mean any incoming record should overwrite whatever happens to match.
A reliable design starts with the business identity of the record. This guide explains conflict targets, duplicate batch inputs, concurrency, and error handling so the statement preserves the intended invariant rather than hiding ambiguous data ownership.
Define the key before writing the statement
Identify what makes one record the same logical entity as another. An external identifier may be unique globally, or only within a tenant or source system. Choose a database constraint or suitable unique index that matches that identity.
Do not use a convenient descriptive field such as a display name when the business allows duplicates or renaming. An incorrect conflict key can merge unrelated records. Conversely, a key that omits part of the identity can let one tenant overwrite another’s state.
Review nullable values and normalization. If the application considers two inputs equivalent after case folding or another transformation, the database definition and input handling need to reflect the approved rule. State the invariant explicitly before designing the conflict clause.
Choose the conflict action deliberately
DO NOTHING can be appropriate when a duplicate should leave existing state unchanged. DO UPDATE expresses a different policy: selected fields are changed when the supported conflict occurs. Neither should be chosen solely to avoid handling an error.
Specify which fields may change and which must remain authoritative. Creation time, ownership, and privileged state often deserve different handling from an ordinary descriptive value. A blanket update of every incoming field can bypass intended business transitions.
The excluded row represents proposed insertion values in the documented syntax. Use it carefully alongside existing values. Decide whether absent information means keep the old value, clear it, or reject the request. Those meanings are application rules, not automatic UPSERT semantics.
Understand the supported conflict target
PostgreSQL can infer a suitable unique index from a conflict target or use a supported named constraint. Read the restrictions for ON CONFLICT DO UPDATE, including supported arbiters. An arbitrary CHECK or foreign-key violation is not converted into an update by the clause.
Partial unique indexes require attention to predicate matching and inference. Confirm the actual database definition and the intended conflict coverage. A statement that works for one row category may not handle another category the same way.
Keep schema changes coordinated with application statements. Renaming or replacing a constraint can affect a statement that names it directly. A migration must verify the deployed query and the new index or constraint together.
Use a narrow illustrative statement
For a fictional contact table whose identity is tenant plus external ID, a reviewed pattern might look like this:
INSERT INTO contacts (tenant_id, external_id, display_name)
VALUES (1, 'demo-17', 'Example')
ON CONFLICT (tenant_id, external_id)
DO UPDATE SET display_name = EXCLUDED.display_name;This illustrates syntax, not production authorization. Real input should be supplied through parameterized queries, and the application must establish which tenant the caller may affect. A composite conflict key helps data identity but does not replace access control.
Test the update behavior separately from insertion. The existing row may contain state that the incoming source is not authorized to change. Keep the statement’s fields aligned with that authority.
Deduplicate batches according to business rules
ON CONFLICT DO UPDATE is deterministic in PostgreSQL’s documented sense and cannot affect the same existing row more than once within one statement. A batch containing multiple proposed rows with the same relevant key can therefore produce a cardinality error.
Resolve duplicate inputs intentionally before issuing the batch. Choose whether first wins, last wins, values combine, or duplicates are invalid. Preserve enough source context to explain that choice. Arbitrary list order should not silently determine business state.
Test both a clean batch and conflicting batch entries. Also test conflicts with already-stored rows. Batch-level deduplication and database uniqueness protect different stages of the workflow.
Review concurrency and update conditions
ON CONFLICT provides documented atomic insert-or-update behavior for its supported action, but surrounding application logic can still race. A prior lookup followed by a later statement may be stale. Keep required conditions in the appropriate database operation where feasible.
A conditional update can limit when an existing row is changed. Read its locking and result behavior carefully: a row can participate in conflict handling without being updated if the condition does not pass. Do not equate no returned row with an unknown database failure.
Consider version or timestamp rules for event ingestion. An old event should not necessarily overwrite newer state. Use a domain-appropriate ordering rule and test out-of-order delivery rather than assuming arrival order equals business order.
Handle results and side effects explicitly
Use supported RETURNING behavior when the caller needs values produced by the operation. Decide what the application should report for insertion, update, ignored duplicate, or rejected condition. Do not infer the business outcome from an unreliable client-side row count assumption.
Keep external effects separate. Sending a notification for every UPSERT attempt can duplicate messages even when the row state remains correct. Coordinate event publication or other actions through an appropriate transactional workflow.
Authorization policies, triggers, and other constraints can affect the statement. Test through the actual application role and schema rather than only a privileged administrative connection. The real deployment boundary is where correctness matters.
A practical synchronization scenario
Suppose a service imports customer display names from an approved source. Its logical key is source plus tenant plus external ID. Review that key, reject ambiguous duplicates in each batch, and update only fields the source owns.
Next, send an older event after a newer event and verify the ordering rule. Run two concurrent attempts with the same key and inspect final state. These tests establish more useful evidence than a single successful insert followed by a single update.
Record the identity, allowed fields, duplicate-input policy, and outcome handling in the integration contract. A concise SQL statement should implement those decisions, not conceal their absence.
Frequently asked questions
Does ON CONFLICT handle every constraint failure?
No. Its conflict handling relies on supported uniqueness arbiters and documented actions. Other failures still need appropriate handling.
Can one batch update the same row repeatedly?
ON CONFLICT DO UPDATE does not allow one statement to affect the same existing row more than once. Define a batch deduplication policy.
Where can I check exact semantics?
Read the PostgreSQL INSERT reference. For designing the underlying invariant, see our PostgreSQL constraints guide.
