PostgresSQL sequence id index didnt match and unsynced

Deutomo

— 1 min read

So when try to add a row on the database that already have many id on that database got error because the id is already exist that it was not sequencing from the last index.

Example i add a new row and error the id is already existed and it was duplicated at 5829. After i check the database the last index of the database is 46721. So we need to resync the index. This is how to do it:

First: Check the current max ID

SELECT MAX(id) FROM your_table

Second: Check the current sequence value

SELECT last_value FROM your_table_id_seq

if you didnt now or unsure with the table id_seq, just use

SELECT pg_get_serial_sequence('your_table','id')

Third: Reset the sequenct to the correct value

SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table));

Fourth: Try to Insert a row

INSERT INTO your_table(column1,column2,...) VALUES (values1,values2,....);

Read more