How to re-sync auto-increment in PostgreSQL

#sql #postgres

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!

Latest comments (0)

An Animated Guide to Node.js Event Loop

Node.js doesn’t stop from running other operations because of Libuv, a C++ library responsible for the event loop and asynchronously handling tasks such as network requests, DNS resolution, file system operations, data encryption, etc.

What happens under the hood when Node.js works on tasks such as database queries? We will explore it by following this piece of code step by step.