Postgres Database sequence out of sync

Hi there,

You could give the following a try:

• Take a backup of your database so that you could revert to it in case anything goes wrong!
• Login to psql and run the following
• Get the max value from your table:

SELECT MAX(id) FROM your_table;

• Then run the following, which should be higher than the last result:

SELECT nextval('your_table_id_seq');

• If it’s not higher, run this set the sequence last to your highest id.

Make sure to run a quick pg_dump first!

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

Remember to be very careful when manipulating sequences and other database objects, as mistakes can lead to data corruption or loss.

Let me know how it goes!

Best,

Bobby