How to Fix a Sequence When It Goes Out of Sync in Postgres | by Arctype | Feb, 2022

Resolve the out-of-sync sequence drawback in Postgres

How to fix a sequence when it goes out of sync in Postgres

What’s a Postgres sequence?

The official documentation explains {that a} sequence is nothing greater than a quantity generator. Specifically, Postgres makes use of a sequence to generate a progressive quantity, which normally represents an robotically incremented numeric main key.

When do sequences exit of sync?

Primarily based on my expertise as a senior software program developer, there are three the reason why a Postgres sequence can exit of sync. Particularly, this occurs when:

  • manually setting the improper worth of a sequence with the setval() perform;
  • inserting a brand new file right into a desk by manually specifying the id subject within the INSERT question.

How do I determine this challenge?

Recognizing this challenge is easy. While you specify no worth for the id column and manually launch an INSERT question or let your ORM (Object-Relational Mapping) do it for you, Postgres will at all times return a “duplicate key worth violates distinctive constraint” error. Additionally, it will by no means occur when performing UPDATE queries.

How to fix a sequence when it goes out of sync in Postgres

It’s now time to see methods to handle the out-of-sync drawback and resolve it as soon as and for all. This drawback might be addressed in a couple of methods. Listed here are two approaches.

Technique 1: Single desk resolution

Suppose you need to repair the sequence related to the id column of your Customers desk. You possibly can obtain this by operating the next question:

SELECT SETVAL('public."Users_id_seq"', COALESCE(MAX(id), 1)) FROM public."Customers";

Technique 2: Fixing all of your sequences with one script

In the event you wished to repair all of your sequences with one question, you could possibly use the next script coming from the official Postgres Wiki:

quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
pg_depend AS D,
pg_class AS T,
pg_attribute AS C,
pg_tables AS PGT
WHERE S.relkind = 'S'
AND S.oid = D.objid
AND D.refobjid = T.oid
AND D.refobjid = C.attrelid
AND D.refobjsubid = C.attnum
AND T.relname = PGT.tablename
ORDER BY S.relname;
How to fix a sequence when it goes out of sync in Postgres
  1. Run the question contained within the fix_sequences.sql file and retailer the end in a temp file. Then, run the queries contained within the temp file. Lastly, delete the temp file. You possibly can obtain this with the next three instructions:
psql -Atq -f fix_sequences.sql -o temp
psql -f temp
rm temp

Now, all it’s a must to do to confirm that your sequence is now not out-of-sync is to insert a brand new file in the identical desk the place you initially skilled the difficulty.

INSERT INTO "Customers"("id", "identify", "surname", "e mail", "password")
VALUES (DEFAULT, 'Jennifer', 'Jones', '', 'pBHxe*cWnC2ZJKHw');
How to fix a sequence when it goes out of sync in Postgres

Postgres is undoubtedly an excellent RDBMS. On the similar time, it has a couple of points that may waste your time.

More Posts