Goal Find simple primary keys that column name does not contain the table name. The naming should be clear and consistent.
Notes The query excludes primary key columns that are also foreign key columns because the name of these columns could be derived based on another (referenced) table.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Low (Many false-positive results)
License MIT License
Fixing Suggestion Rename the column so that the column name contains the table name.
Data Source system catalog only
SQL Query
WITH simple_pk AS (SELECT 
n.nspname AS table_schema,
c.relname as table_name, 
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE) AS column_name
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND cardinality(o.conkey)=1 
AND o.contype = 'p' 
AND c.relkind = 'r'
),
simple_fk AS (SELECT 
n.nspname AS table_schema,
c.relname as table_name, 
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE) AS column_name
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND cardinality(o.conkey)=1 
AND o.contype = 'f' 
AND c.relkind = 'r'
)
SELECT table_schema, table_name, column_name 
FROM simple_pk
WHERE  column_name NOT ILIKE '%' || table_name || '%'
AND NOT EXISTS (SELECT *
FROM simple_fk
WHERE simple_pk.table_schema=simple_fk.table_schema
AND simple_pk.table_name=simple_fk.table_name
AND simple_pk.column_name=simple_fk.column_name)
ORDER BY table_schema, table_name;

Collections

This query belongs to the following collections:

NameDescription
Find problems about namesA selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview.
Categories

This query is classified under the following categories:

NameDescription
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
NamingQueries of this category provide information about the style of naming.
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.