Change Django AutoField Initial Value

Question

I have an existing database that I migrated from SQLAlchemy to the new PostgreSQL database.

I moved all primary keys with the same values ​​as before. Now I have tables filled with data, but the corresponding sequences start with 1. I have pk values ​​stored from 1 to 2000.

Now when I try to save something with Django, I have

a duplicate key value violates a unique restriction on the Primary key.

How to change the initial values ​​of a sequence or to avoid this situation?
My current solution:

conn = psycopg2.connect(...) for table_name in table_names: cursor = conn.cursor() cursor.execute(""" SELECT setval('%s_id_seq', (SELECT COALESCE(MAX(id),0)+1 FROM %s)); """% (table_name, table_name)) 

It works for me, but I don't like it.

Answer 1

Ways to set / reset sequence in PostgreSQL

(optional up to max(id) ).

• Here is a simple way that you have in the question. You can set a sequence starting with an arbitrary number using setval() :

   SELECT setval('tbl_id_seq'); 

• Then there is a standard SQL path with ALTER SEQUENCE that does the same:

   ALTER SEQUENCE myseq RESTART WITH 1; 

• If you want to restart your sequences with numbers other than the default value of 1:

   CREATE TABLE foo(id serial, a text); -- creates sequence "foo_id_seq" INSERT INTO foo(a) VALUES('a'); -- seq. starts with --> 1 ALTER SEQUENCE foo_id_seq START WITH 10; -- doesn't restart sequence INSERT INTO foo(a) VALUES('b'); --> 2 ALTER SEQUENCE foo_id_seq RESTART; -- restarts sequence INSERT INTO foo(a) VALUES('c'); --> 10 

• And there is another way when you clear the TRUNCATE table:

   TRUNCATE foo RESTART IDENTITY; 

  Implicitly executes ALTER SEQUENCE foo_id_seq RESTART;