Does a Postgresql dump create sequences that start with - or after - the last key?

Question

I recently created a SQL dump of a database behind a Django project, and after cleaning the SQL up a little bit was able to restore the DB and all of the data. The problem was the sequences were all mucked up. I tried adding a new user and generated the Python error IntegrityError: duplicate key violates unique constraint.

Naturally I figured my SQL dump didn't restart the sequence. But it did:

DROP SEQUENCE "auth_user_id_seq" CASCADE;
CREATE SEQUENCE "auth_user_id_seq" INCREMENT 1 START 446 MAXVALUE 9223372036854775807 MINVALUE 1 CACHE 1;
ALTER TABLE "auth_user_id_seq" OWNER TO "db_user";

I figured out that a repeated attempt at creating a user (or any new row in any table with existing data and such a sequence) allowed for successful object/row creation. That solved the pressing problem.

But given that the last user ID in that table was 446 - the same start value in the sequence creation above - it looks like Postgresql was simply trying to start creating rows with that key.

Does the SQL dump provide the wrong start key by 1? Or should I invoke some other command to start sequences after the given start ID? Keenly curious.

Answer 1

The dump is fine, no problem. If your code (or default value for the column) uses nextval() to get the next value from the sequence, everything will be fine as well. Check your code and see what it does, what SQL is executed. With this information you can see why things are going wrong.

Good luck!