Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.2k views
in Technique[技术] by (71.8m points)

postgresql - Modify Django AutoField start value

I have and existing database, which I have migrated with SQLAlchemy to a new PostgreSQL database.

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

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

duplicate key value violates unique constraint regarding to the Primary Key.

How can I modify the sequence start values or escape this situation?
My current solution is:

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Ways to set / reset a sequence in PostgreSQL

(not necessarily to max(id)).

  • There's the simple way you have in the question. You can set the sequence to start at an arbitrary number with setval():

    SELECT setval('tbl_id_seq');
    
  • Then there's the standard SQL way with ALTER SEQUENCE doing the same:

    ALTER SEQUENCE myseq RESTART WITH 1;
    
  • If you like to restart your sequences at numbers other than the default 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 empty a table with TRUNCATE:

    TRUNCATE foo RESTART IDENTITY;
    

    Implicitly executes ALTER SEQUENCE foo_id_seq RESTART;


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...