#TIL 21 - How to fix PostgreSQL duplicate key violates (out of sync)

What

Fix "duplicate key violates unique constraint" when primary key sequence is out of sync.

Why

Sequence may be behind actual data due to manual inserts or imports.

How

Check current state:

SELECT nextval('public.source_id_seq'), MAX(id) FROM "source";

Reset sequence:

SELECT SETVAL(
  (SELECT PG_GET_SERIAL_SEQUENCE('"source"', 'id')),
  (SELECT (MAX("id") + 1) FROM "source"),
  FALSE
);