Id sequence is not incrementing after inserting a row with id

Question

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?

The sqecuence is only used if you do not explicitly give the id — Jens, Sep 07 '22 at 08:04
Please share more details. Is this a MySQL problem, or a PostgreSQL problem? And how is this related to [tag:create-table]? — Nico Haase, Sep 07 '22 at 08:09
You're not supposed to insert an id value when the column already has a nextval default value. Simply do `INSERT INTO user (firstname,lastname) VALUES ('firstname', 'lastname')`. — jarlh, Sep 07 '22 at 08:13
A `default` value is used when **no** value is specified during an INSERT. You are providing a value for the `id` column, so the `default` isn't used. Additionally: the `max(id)+1` approach will fail if you have concurrent transactions. And finally: `identity` columns are the recommended approach to auto-generate primary key values — , Sep 07 '22 at 08:23
@a_horse_with_no_name Is there any way I could get the sequence updated even if someone gives id? or by changing Id Default value? — Suhan, Sep 07 '22 at 08:25
https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync — , Sep 07 '22 at 08:28
yeah i reset the sequence at once but how can I maintain sequence updated even if someone insert along id in future? — Suhan, Sep 07 '22 at 10:37

Laurenz Albe · Answer 1 · 2022-09-08T08:30:03.693

1

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.

edited Sep 08 '22 at 08:30

answered Sep 07 '22 at 10:40

Laurenz Albe

how to start identity with max(id)+1. Because it start with Id=1 and gives ```violates unique constraint 'user_pkey'``` – Suhan Sep 07 '22 at 18:27
I don't understand. If it is a new column, why do you have to start with a higher value? – Laurenz Albe Sep 07 '22 at 19:09
It isn't a new column. It is a existing table where id sequence is not getting updated – Suhan Sep 08 '22 at 08:06
I see. I changed the answer to modify an existing column, including a higher start value. – Laurenz Albe Sep 08 '22 at 08:31

1 Answers1