fix PostgreSQL serial sequences

SYNOPSYS

When tickling your PostgreSQL with pgadmin you are more likely going to once broke your serial sequences.
This trick allows you to detect the messed sequences and correct them.

PROCEDURE

show sequences

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


-- psql -Atq -f show_sequences.sql -o tmp.sql && psql -Atq -f tmp.sql
SELECT
  'SELECT ' ||quote_literal(S.relname)|| ' as seq_name' ||
  ', (select last_value from ' ||S.relname|| ') as seq_val' ||
  ', (select MAX('||quote_ident(C.attname)|| ') FROM ' ||T.relname|| ') as max_id;'

FROM
  pg_class AS S
  ,pg_depend AS D
  ,pg_class AS T
  ,pg_attribute AS C
WHERE
  S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
ORDER BY S.relname;

fix sequences

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


-- psql -Atq -f fix_sequences.sql -o tmp.sql && psql -Atq -f tmp.sql
SELECT
  'SELECT SETVAL(' ||quote_literal(S.relname)||
  ', MAX(' ||quote_ident(C.attname)||
  ') ) FROM ' ||quote_ident(T.relname)|| ';'
FROM
  pg_class AS S
  ,pg_depend AS D
  ,pg_class AS T
  ,pg_attribute AS C
WHERE
  S.relkind = 'S'
  AND S.oid = D.objid
  AND D.refobjid = T.oid
  AND D.refobjid = C.attrelid
  AND D.refobjsubid = C.attnum
ORDER BY S.relname;