How to re-sync auto-increment in PostgreSQL

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 purchases_id_seq.

If you have your table inside a schema, following the previous example, having your table inside of ecommerce schema, the name would be ecommerce."purchases_id_seq".

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!