Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with exactly one column

WITH key_constraint_names AS (
select 
CASE WHEN contype='p' THEN 'PRIMARY KEY' ELSE 'UNIQUE' END AS type,
o.conname AS constraint_name,
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 nc on nc.oid=c.relnamespace
inner join  pg_authid AS a on nc.nspowner=a.oid
where (nc.nspname='public' or rolname<>'postgres')
and o.contype IN ('u','p')  and cardinality(o.conkey)=1),

chk_constraint_names AS (select 
'CHECK' AS type,
o.conname AS constraint_name,
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 nc on nc.oid=c.relnamespace
inner join  pg_authid AS a on nc.nspowner=a.oid
where (nc.nspname='public' or rolname<>'postgres')
and o.contype = 'c'  and cardinality(o.conkey)=1),

exclude_constraint_names AS (select 
'EXCLUDE' AS type,
o.conname AS constraint_name,
c.relname as table_name, 
coalesce((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 nc on nc.oid=c.relnamespace
inner join  pg_authid AS a on nc.nspowner=a.oid
where (nc.nspname='public' or rolname<>'postgres')
and o.contype = 'x'  and cardinality(o.conkey)=1),

fk_constraint_names AS (select 
'FOREIGN KEY' AS type,
o.conname,
(select nspname from pg_namespace where oid=f.relnamespace) as foreign_ns,
f.relname as foreign_table,
(select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false) as foreign_colname,
(select nspname from pg_namespace where oid=c.relnamespace) as target_ns,
c.relname as target_table, 
(select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as target_colname
from pg_constraint o inner join   pg_class c on c.oid = o.conrelid
inner join pg_class f on f.oid = o.confrelid
inner join pg_namespace as nc on nc.oid=c.relnamespace
inner join  pg_authid AS a on nc.nspowner=a.oid
where (nc.nspname='public' or rolname<>'postgres')
and o.contype = 'f'  and cardinality(o.conkey)=1),

index_without_col_name AS (select 
'INDEX' AS type,
ci.relname as index_name,
n.nspname as schema_name,
i.indrelid as table_oid,
ct.relname as table_name,
unnest(indkey) as col_seq
from pg_catalog.pg_index i inner join pg_catalog.pg_class ci on i.indexrelid=ci.oid
inner join pg_catalog.pg_class ct on i.indrelid=ct.oid
inner join pg_catalog.pg_namespace n on ct.relnamespace=n.oid
where indnatts=1 and indisunique=false and 
n.nspname  NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),

index_with_col_name AS (
select type, index_name, schema_name, table_name, a.attname as column_name
from index_without_col_name i inner join pg_catalog.pg_attribute a on i.table_oid=a.attrelid and i.col_seq=a.attnum)

SELECT pattern, string_agg(DISTINCT type,';' ORDER BY type DESC) AS types, Count(*) AS number_of_occurrences, CASE WHEN Count(*)<5 THEN string_agg(constraint_name || ' (' || pattern_source || ')',';<br>' ORDER BY constraint_name ASC) ELSE NULL END AS names
FROM (SELECT 
CASE WHEN (lower(column_name) IN ('id','code') OR lower(column_name)=lower(table_name)) THEN
replace(replace(lower(constraint_name), lower(table_name),'<b>table</b>'), lower(column_name), '<b>column</b>')
ELSE
replace(replace(lower(constraint_name), lower(column_name),'<b>column</b>'), lower(table_name), '<b>table</b>') END AS pattern, type, constraint_name, table_name || '.' || column_name AS pattern_source
FROM key_constraint_names

UNION ALL SELECT replace(replace(lower(constraint_name), lower(column_name),'<b>column</b>'), lower(table_name), '<b>table</b>') AS pattern, type, constraint_name, 
case when column_name='' then table_name else table_name || '.' || column_name end AS pattern_source
FROM exclude_constraint_names

UNION ALL SELECT replace(replace(lower(index_name), lower(column_name),'<b>column</b>'), lower(table_name), '<b>table</b>') AS pattern, type, index_name, table_name || '.' || column_name AS pattern_source
FROM index_with_col_name

UNION ALL SELECT replace(replace(lower(constraint_name), lower(column_name),'<b>column</b>'), lower(table_name), '<b>table</b>') AS pattern, type, constraint_name, table_name || '.' || column_name AS pattern_source
FROM chk_constraint_names

UNION ALL SELECT 

CASE 
WHEN (foreign_table ILIKE '%' || target_table || '%' AND foreign_table<>target_table) THEN
replace(replace(replace(lower(conname), lower(target_colname), '<b>column</b>'), lower(foreign_table), '<b>foreign_table</b>'), lower(target_table), '<b>table</b>')
ELSE
replace(replace(replace(lower(conname), lower(target_colname), '<b>column</b>'), lower(target_table), '<b>table</b>'), lower(foreign_table), '<b>foreign_table</b>') 
END AS pattern, 
type, conname, 'primary: ' || foreign_table || '.' || foreign_colname || ' child: '  || target_table || '.' || target_colname AS pattern_source
FROM fk_constraint_names

) AS foo
GROUP BY pattern
ORDER BY types, Count(*) DESC, pattern;

Query goal:	Find patterns of the names of constraints and indexes. Make sure that the naming is consistent.
Notes about the query:	The query replaces in the constraint/index name table and column names with keywords column, table, foreign table. These words are between (b) tags to improve readability of the result in the web. In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser. If the number of pattern occurrences is smaller than five, then the query presents constraint name as well as column names and table names with which the constraint is associated. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog.
Query type:	General (Overview of some aspect of the database.)
Query license:	MIT License
Data source:	INFORMATION_SCHEMA+system catalog
SQL query:	Click on query to copy it WITH key_constraint_names AS ( select CASE WHEN contype='p' THEN 'PRIMARY KEY' ELSE 'UNIQUE' END AS type, o.conname AS constraint_name, 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 nc on nc.oid=c.relnamespace inner join pg_authid AS a on nc.nspowner=a.oid where (nc.nspname='public' or rolname<>'postgres') and o.contype IN ('u','p') and cardinality(o.conkey)=1), chk_constraint_names AS (select 'CHECK' AS type, o.conname AS constraint_name, 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 nc on nc.oid=c.relnamespace inner join pg_authid AS a on nc.nspowner=a.oid where (nc.nspname='public' or rolname<>'postgres') and o.contype = 'c' and cardinality(o.conkey)=1), exclude_constraint_names AS (select 'EXCLUDE' AS type, o.conname AS constraint_name, c.relname as table_name, coalesce((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 nc on nc.oid=c.relnamespace inner join pg_authid AS a on nc.nspowner=a.oid where (nc.nspname='public' or rolname<>'postgres') and o.contype = 'x' and cardinality(o.conkey)=1), fk_constraint_names AS (select 'FOREIGN KEY' AS type, o.conname, (select nspname from pg_namespace where oid=f.relnamespace) as foreign_ns, f.relname as foreign_table, (select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false) as foreign_colname, (select nspname from pg_namespace where oid=c.relnamespace) as target_ns, c.relname as target_table, (select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false) as target_colname from pg_constraint o inner join pg_class c on c.oid = o.conrelid inner join pg_class f on f.oid = o.confrelid inner join pg_namespace as nc on nc.oid=c.relnamespace inner join pg_authid AS a on nc.nspowner=a.oid where (nc.nspname='public' or rolname<>'postgres') and o.contype = 'f' and cardinality(o.conkey)=1), index_without_col_name AS (select 'INDEX' AS type, ci.relname as index_name, n.nspname as schema_name, i.indrelid as table_oid, ct.relname as table_name, unnest(indkey) as col_seq from pg_catalog.pg_index i inner join pg_catalog.pg_class ci on i.indexrelid=ci.oid inner join pg_catalog.pg_class ct on i.indrelid=ct.oid inner join pg_catalog.pg_namespace n on ct.relnamespace=n.oid where indnatts=1 and indisunique=false and n.nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)), index_with_col_name AS ( select type, index_name, schema_name, table_name, a.attname as column_name from index_without_col_name i inner join pg_catalog.pg_attribute a on i.table_oid=a.attrelid and i.col_seq=a.attnum) SELECT pattern, string_agg(DISTINCT type,';' ORDER BY type DESC) AS types, Count() AS number_of_occurrences, CASE WHEN Count()<5 THEN string_agg(constraint_name \|\| ' (' \|\| pattern_source \|\| ')',';<br>' ORDER BY constraint_name ASC) ELSE NULL END AS names FROM (SELECT CASE WHEN (lower(column_name) IN ('id','code') OR lower(column_name)=lower(table_name)) THEN replace(replace(lower(constraint_name), lower(table_name),'<b>table</b>'), lower(column_name), '<b>column</b>') ELSE replace(replace(lower(constraint_name), lower(column_name),'<b>column</b>'), lower(table_name), '<b>table</b>') END AS pattern, type, constraint_name, table_name \|\| '.' \|\| column_name AS pattern_source FROM key_constraint_names UNION ALL SELECT replace(replace(lower(constraint_name), lower(column_name),'<b>column</b>'), lower(table_name), '<b>table</b>') AS pattern, type, constraint_name, case when column_name='' then table_name else table_name \|\| '.' \|\| column_name end AS pattern_source FROM exclude_constraint_names UNION ALL SELECT replace(replace(lower(index_name), lower(column_name),'<b>column</b>'), lower(table_name), '<b>table</b>') AS pattern, type, index_name, table_name \|\| '.' \|\| column_name AS pattern_source FROM index_with_col_name UNION ALL SELECT replace(replace(lower(constraint_name), lower(column_name),'<b>column</b>'), lower(table_name), '<b>table</b>') AS pattern, type, constraint_name, table_name \|\| '.' \|\| column_name AS pattern_source FROM chk_constraint_names UNION ALL SELECT CASE WHEN (foreign_table ILIKE '%' \|\| target_table \|\| '%' AND foreign_table<>target_table) THEN replace(replace(replace(lower(conname), lower(target_colname), '<b>column</b>'), lower(foreign_table), '<b>foreign_table</b>'), lower(target_table), '<b>table</b>') ELSE replace(replace(replace(lower(conname), lower(target_colname), '<b>column</b>'), lower(target_table), '<b>table</b>'), lower(foreign_table), '<b>foreign_table</b>') END AS pattern, type, conname, 'primary: ' \|\| foreign_table \|\| '.' \|\| foreign_colname \|\| ' child: ' \|\| target_table \|\| '.' \|\| target_colname AS pattern_source FROM fk_constraint_names ) AS foo GROUP BY pattern ORDER BY types, Count(*) DESC, pattern;

Collection name	Collection description
Find problems by overview	Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Lexicon bad smells and linguistic antipatterns	Queries made to find the occurrences of lexicon bad smells and linguistic antipatterns

Category name	Category description
CHECK constraints	Queries of this category provide information about CHECK constraints.
Comfortability of database evolution	Queries of this category provide information about the means that influence database evolution.
Inconsistencies	Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Naming	Queries of this category provide information about the style of naming.
Relationships between tables	Queries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly.
Uniqueness	Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.

Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with exactly one column

Collections where the query belongs to

Categories where the query belongs to

Reference materials for further reading