This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find orphaned Sequence objects which not owned by any column of PostgreSQL Database.
When we define a column using SERIAL or BIGSERIAL, PostgreSQL internally creates one sequence for it and associate with it.
In RDBMS, A Sequence is an independent object, and you can use same sequence object for multiple tables as well.
In a development environment, we are executing DDL frequently commands like multiple CREATE or DROP statements, so there is possibility of Orphaned Sequence.
A Database Administrator can use this script to remove unwanted Sequence and make sure that Postgres database is up to date.
|
1 2 3 4 5 6 7 8 9 |
SELECT ns.nspname AS SchemaName ,c.relname AS SequenceName FROM pg_class AS c JOIN pg_namespace AS ns ON c.relnamespace=ns.oid WHERE c.relkind = 'S' AND NOT EXISTS (SELECT * FROM pg_depend WHERE objid=c.oid AND deptype='a') ORDER BY c.relname; |
Leave a Reply