Id sequence is not incrementing after inserting a row with id

Suhan 0
Viewed 56

I have table user (id, firstname, lastname) and id is defined as

id int8 NOT NULL DEFAULT nextval('user_id_seq'::regclass)

But when I first insert a row through database using this SQL:

INSERT INTO user (id, firstname, lastname) 
VALUES((SELECT(MAX(id) + 1) FROM user), firstname, lastname);

the data gets inserted, but when I am hitting through API then id is not returned, I get an error

duplicate key value violates unique constraint 'user_pkey'

This is because in the previous insertion through database sequence is not updated.

How to resolve this?

ddl sql postgresql
1 Answers

The only good way to prevent that is to use an identity column instead:

ALTER TABLE tab
   ALTER id
   ADD GENERATED ALWAYS AS IDENTITY (START 1000000);

That automatically creates a sequence, and a normal insert statement is not allowed to override the default.

Laurenz Albe0
Related