First, to check if your auto-increment is out of sync, run the following:
select max(id) from table; select nextval('table_id_seq')
If the result from
nextval is not greater than the result of your
max(id), your auto-increment is out of sync.
table_id_seq is the name of the sequence, which is composed of
table name _ column with auto-increment _ seq. If you have a table
purchases where the
id is the auto-increment column, then the sequence name would be
If you have your table inside a schema, following the previous example, having your table inside of
ecommerce schema, the name would be
You can check all your sequences with the following statement :
select * from information_schema.sequences
Here's the statement to fix it, using our example names:
select setval('ecommerce."purchases_id_seq"', coalesce( (select max(id)+1 from ecommerce.purchases), 1), false)
Hope this was helpful!