DEV Community 👩‍💻👨‍💻

Daniel Cruz
Daniel Cruz

Posted on • Updated on

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')
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Hope this was helpful!

Top comments (0)

We are hiring! Do you want to be our Senior Platform Engineer? Are you capable of chipping in across sysadmin, ops, and site reliability work, while supporting the open source stack that runs DEV and other communities?

This role might just be for you!

Apply now