Postgresql - Autoincrement ID

Question

I am using Postgresql v13.4 and I want to update an existing and have got some problems to keep the existing ID when there is a conflict.

I am creating the following table which is successfully shown in Postgresql:

 query = """
    DROP SEQUENCE user_id_seq cascade;
    Create sequence user_id_seq;    
    CREATE TABLE customers3 (        
    "customer_id" bigint PRIMARY KEY NOT NULL DEFAULT nextval('user_id_seq'),    
    "name" VARCHAR,
    "age" Integer,
    "insdat" Integer NOT NULL,
    "upddat" Integer NOT NULL,
    constraint test unique(name)
 )
"""

Once I insert the data the output is following:

####################################
## Insert data
(0, 'Paul', 42, 2021, 2021)
(1, 'Peter', 23, 2021, 2021)
(2, 'Mary', 33, 2021, 2021)

Now I want to insert the case which results into the FIRST error:

 INSERT INTO customers3 (name, age, insdat, upddat) VALUES ('Adam', 24, 2022, 2022)
         ON CONFLICT ON CONSTRAINT test do update set  age = 24, upddat = 2022;
 Database connection failed due to (psycopg2.errors.UniqueViolation) duplicate key  value violates unique constraint "customers3_pkey"  
 DETAIL:  Key (customer_id)=(1) already exists.

I would like to insert more cases not only this one and there are double IDs possible. Hence I like not to insert the ID in the query ..

For some reason which I do not understand why the autoincrement does not work correctly.

You probably ran some inserts bypassing the default value and thus the sequence is out of sync with the table's content. That's why using `generated always as identity` is the better solution for auto increment columns — , May 04 '22 at 11:25

score 0 · Answer 1 · answered May 04 '22 at 18:18

0

Using 'int GENERATED ALWAYS AS IDENTITY primary key' as for my issue helped.

answered May 04 '22 at 18:18

Andi Maier

914
3
9
28

Postgresql - Autoincrement ID

1 Answers1