Goal Find sequence generators that are not owned by a table column, i.e., if one drops the table or the column, then the sequence generator stays in place.
Notes The query checks as to whether a sequence depends on a column so that if the table/column is dropped, then the sequence will be dropped as well. The query also takes into account that if a column is an identity column, then internally an associated sequence generator is created that will be dropped if the table/column will be dropped.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT License
Fixing Suggestion Alter the sequence so that it will be owned by a column.
Data Source system catalog only
SQL Query
WITH sequences AS (
SELECT 
c.oid,
n.nspname AS schema_name, 
c.relname AS sequence_name
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind='S'
)
SELECT schema_name, sequence_name
FROM sequences seq
WHERE NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d 
WHERE d.objid=seq.oid
AND d.deptype IN ('a','i')
AND d.refobjsubid<>0)
ORDER BY schema_name, sequence_name;

Collections

This query belongs to the following collections:

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories

This query is classified under the following categories:

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Sequence generatorsQueries of this category provide information about sequence generators and their usage.