DO NOTHING, DO UPDATE, or DO SELECT?
PostgreSQL 19 adds a third upsert option. Answer three quick questions and get the exact ON CONFLICT clause for your use case, with working SQL.
How UpsertPicker works
- 1
Do you need the row back?
If you just want to skip duplicates silently, DO NOTHING is the answer. No further questions needed.
- 2
Do you need to modify the existing row?
If you need to update columns on the conflicting row (e.g. set updated_at to NOW()), only DO UPDATE supports this.
- 3
How performance-sensitive is this path?
For "get or create" patterns where you just want the existing row back unchanged, DO SELECT (PG19+) avoids the dead tuple overhead of the traditional DO UPDATE workaround.
- 4
Get working SQL
The recommendation includes a ready-to-adapt SQL template with the right clause, RETURNING usage, and version compatibility notes.
What each clause does
DO NOTHING
PG 9.5+Silently skips the insert. The existing row is untouched, no dead tuple is created, and RETURNING yields no rows for the skipped insert. Use when you just want idempotent inserts with no side effects.
DO UPDATE
PG 9.5+Updates the existing row with values from the SET clause. Always generates a dead tuple (the old version of the row), even if the new values are identical. Returns the updated row with RETURNING. The only option when you genuinely need to modify the conflicting row.
DO SELECT (new in PG19)
PG 19+Returns the existing row unchanged, with no update and no dead tuple. Designed for the "get or create" pattern. PostgreSQL benchmarks show roughly 4x better throughput than the no-op DO UPDATE workaround for high conflict rate workloads.
ON CONFLICT syntax reference
DO NOTHING
INSERT INTO table (col1, col2)
VALUES (val1, val2)
ON CONFLICT (unique_col)
DO NOTHING;DO UPDATE
INSERT INTO table (col1, col2)
VALUES (val1, val2)
ON CONFLICT (unique_col)
DO UPDATE SET
col2 = EXCLUDED.col2
RETURNING *;DO SELECT (PostgreSQL 19+)
INSERT INTO table (col1, col2)
VALUES (val1, val2)
ON CONFLICT (unique_col)
DO SELECT
RETURNING *;When to use each clause
| Scenario | Recommended clause | Why |
|---|---|---|
| Idempotent batch import (skip duplicates) | DO NOTHING | Fastest, no side effects, no dead tuples |
| Upsert with last-write-wins semantics | DO UPDATE | Only option that can modify the existing row |
| Get or create (return existing row unchanged) | DO SELECT | Returns the row without dead tuple overhead (PG19+) |
| Name-to-ID resolution during data loading | DO SELECT | High conflict rate, only needs the existing ID |
| Idempotent API endpoint (create or fetch) | DO SELECT | Client always gets the resource back, no duplicate rows |
| Sync with external system (update timestamps on match) | DO UPDATE | Needs to set updated_at and sync status on conflict |
Frequently Asked Questions
What is the difference between ON CONFLICT DO NOTHING and DO UPDATE?
DO NOTHING silently skips the insert when a conflict is detected. The conflicting row is not modified, and no data is returned (unless you add RETURNING, which returns nothing for skipped rows). It is the fastest option with zero dead tuple overhead.
DO UPDATE modifies the existing row with new values you specify in a SET clause. It can return the updated row with RETURNING. The tradeoff: every conflict generates a dead tuple, even if the new values are identical to the old ones, which can cause table bloat under high conflict rates.
When should I use ON CONFLICT DO SELECT?
Use DO SELECT when you need the existing row back on conflict but do not need to modify it. This is the classic "get or create" pattern: insert a row if it does not exist, return the existing row if it does.
Before PostgreSQL 19, developers used a no-op DO UPDATE SET col = EXCLUDED.col as a workaround. This works but generates a dead tuple on every conflict. DO SELECT avoids that entirely, and PostgreSQL's own benchmarks show it is roughly 4x faster for high conflict rate workloads.
Does ON CONFLICT DO UPDATE cause table bloat?
Yes. Every DO UPDATE conflict creates a new tuple version and marks the old one as dead, even if the values being set are identical. PostgreSQL's MVCC model requires this because any concurrent transaction might still need the old version.
For occasional conflicts, autovacuum handles dead tuple cleanup with no visible impact. For high conflict rate paths (thousands of conflicts per second), the dead tuple accumulation can cause measurable table bloat, increased I/O from larger table scans, and more frequent autovacuum runs. This is the primary reason DO SELECT was added in PostgreSQL 19.
What PostgreSQL version supports DO SELECT?
ON CONFLICT DO SELECT requires PostgreSQL 19, which entered Beta 1 on June 4, 2026. GA is expected in September 2026. If you are on PostgreSQL 18 or earlier, the workaround is DO UPDATE SET col = EXCLUDED.col RETURNING *, which has the same semantics but generates dead tuples.
DO NOTHING and DO UPDATE have been available since PostgreSQL 9.5 (released January 2016).
Can I use RETURNING with all three ON CONFLICT clauses?
RETURNING works with all three clauses, but the behavior differs:
| Clause | RETURNING behavior |
|---|---|
| DO NOTHING | Returns nothing for skipped rows (only returns data for rows that were actually inserted) |
| DO UPDATE | Returns the updated row (including changes from the SET clause) |
| DO SELECT | Returns the existing, unmodified row |
If you need RETURNING to always return a row regardless of whether an insert or conflict occurred, use DO UPDATE or DO SELECT. DO NOTHING with RETURNING only returns rows for successful inserts.
Is my data sent to any server?
No. UpsertPicker is a pure decision-tree tool with no server calls. Your answers drive a static lookup table that returns the matching SQL template. Nothing is sent anywhere.