CODEWITHSUNDEEP
postgresqlconstraintsrestoredump
Valentin Shilin
Valentin Shilin

Reputation: 11

In PostgreSQL dump/restore, auto incremental sequences duplicate violation constraint

I found a funny bug, important a postgresql dump file with psql:

In case if your table has a sequence(auto increment field), for example before import nextvalue of sequence was 10, after the import we add 100 lines and logically nextvalue must be 111. But it is still 11, and of course you will have a constraint violation on the next insert.

The workaround:

  1. get current next value with a select nextval('xxx_seq')
  2. select max(xxx_id) from yyyy

It is give you the max current value of autoincrement id.

Now we need to set the next value to 2) + 1:

SELECT setval('xxx_seq', n+1, true)

Dump into a file:

pg_dump --data-only -h host -p port -U username -a  dbname > outputfilename

Restoring the dump:

psql -h host -U username dbname < outputfilename

I use the db version 9.5.5 .

Upvotes: 1

Views: 2112

Answers (1)

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61546

TL;DR: it's not a bug.

Tables are linked to sequences by the fact that columns may have nextval(sequence_name) as their default value.

A default clause means that when the value is already provided by the INSERT or a COPY, this nextval(...) expression is not evaluated and the sequence is not incremented. That is what happens when you play a dump.

In the general case, such columns are created as SERIAL and the dump files contain setval of corresponding sequences to adjust them afte the load. Your case differs in how you created the tables or what options were passed to pg_dump, but these details don't appear in the question.

Upvotes: 1

Related Questions