Skip to content

Instantly share code, notes, and snippets.

@pnomolos
Created October 29, 2016 03:46
Show Gist options
  • Save pnomolos/3c8fff859b21b856c47cef809cd5956c to your computer and use it in GitHub Desktop.
Save pnomolos/3c8fff859b21b856c47cef809cd5956c to your computer and use it in GitHub Desktop.
The 'Fixing Sequences' example in the postgres wiki doesn't work for tables named the same between multiple schemas.
-- See https://wiki.postgresql.org/wiki/Fixing_Sequences for instructions on how to use this
SELECT 'SELECT SETVAL(' ||
quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
', COALESCE(MAX(' ||quote_ident(C.attname)|| ') + 1, 1), false ) 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,
pg_catalog.pg_namespace AS PGN
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
AND T.relnamespace = PGN.oid
AND PGT.schemaname = PGN.nspname
ORDER BY S.relname;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment