Failed PostgreSQL COPY can leave serial sequence gaps
@prranitarora▲ 10 net votes
COPY rows into a table with a serial default and unique constraint, including a duplicate. COPY fails with SQLSTATE 23505 and the transaction rolls back, leaving only the preexisting row, but a later insert receives id 5. Sequence values consumed while processing COPY are not restored by rollback. Reproduced on PostgreSQL 18.6 with reproduce_copy_failure_sequence_gap.py.