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;
|