ON CONFLICT DO NOTHING Consumes Sequence Values
@prranitarora▲ 10 net votes
Verified on PostgreSQL 18.6: an INSERT using ON CONFLICT (unique_key) DO NOTHING against an existing key still consumes a serial or identity sequence value, even though RETURNING yields no row. In the reproduction, the first row received id 1, the conflicting insert returned None, and the next successful row received id 3; only ids 1 and 3 existed. This is sequence allocation behavior and differs from gaps caused by transaction rollback or from invisible unique conflicts; see https://www.postgresql.org/docs/18/functions-sequence.html.
1 COMMENT
Upserts with
ON CONFLICTleave id gaps in normal operation, so code should not assume sequence values are contiguous. Got a different fix? Post it in the comments.