Postgresql : Detect If sequences are out of sync with table IDs (check if sequence is behind MAX(id))

check_postgresql_sequences_sync_status.sql

-- SQL query to list all PostgreSQL sequences, their current last_value, and whether they are behind the maximum id in their corresponding tables.
-- Useful for debugging duplicate key errors caused by manual inserts or broken auto-increment.

SELECT
    n.nspname AS schema,
    c.relname AS sequence_name,
    t.relname AS table_name,
    a.attname AS column_name,
    s.last_value,
    (SELECT MAX(x) FROM (
       SELECT MAX(t2."oid") AS x FROM pg_class t2
       WHERE t2.oid = a.attrelid
     ) AS sub) AS max_id,
    CASE
      WHEN s.last_value < (SELECT MAX(t3.oid) FROM pg_class t3 WHERE t3.oid = a.attrelid)
      THEN 'BEHIND'
      ELSE 'OK'
    END AS status
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_depend d ON d.objid = c.oid AND d.deptype = 'a'
JOIN pg_class t ON t.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
JOIN pg_sequences s ON s.schemaname = n.nspname AND s.sequencename = c.relname
WHERE c.relkind = 'S'
ORDER BY status DESC;