The list of all the queries

Sequence generators not needed

Query goal: Find possible classifier tables that have a column with a sequence generator. Such tables should have natural keys instead of surrogate keys.
Notes about the query: The query finds base tables that have four or less columns do not have any foreign keys and have a column that is associated with a sequence generator. The query considers both external and internal sequence generators.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: Replace surroggate keys with natural keys. Each classifier has a code and this should be used as the primary key.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH fk AS (SELECT
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS target_schema,
c.relname AS target_table
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_class f ON f.oid = o.confrelid
WHERE o.contype = 'f'),
tables_with_sequences AS (SELECT A.table_schema, A.table_name 
FROM information_schema.columns A LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name
INNER JOIN information_schema.schemata S
ON A.table_schema=S.schema_name
WHERE (coalesce (A.column_default, d.domain_default) ILIKE '%nextval%' OR is_identity='YES')
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres'))
SELECT 
table_schema, 
table_name
FROM information_schema.tables AS t
WHERE table_type='BASE TABLE'
AND table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
AND (SELECT Count(*) AS cnt FROM INFORMATION_SCHEMA.columns AS c
WHERE t.table_schema=c.table_schema
AND t.table_name=c.table_name)<=4
AND (table_schema, table_name) NOT IN (SELECT target_schema, target_table
FROM fk)
AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM tables_with_sequences)
ORDER BY table_schema, table_name;

Collections where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
Classifier tablesQueries of this category provide information about registration of classifiers.
Sequence generatorsQueries of this category provide information about sequence generators and their usage.
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.

The list of all the queries