Find the patterns of the names of columns of simple primary keys. Make sure that the naming is consistent. Ideally, the names should indicate as to whether the column is a surrogate or a natural key column. 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.
Notes
To find patterns the query replaces in the name table name with the lowercase word "table". The word is between (b) tags for the better readability in case the query result is displayed in a web browser. The query only consider primary keys that consist of one column.
Type
General (Overview of some aspect of the database.)
Try to use a naming scheme and do it consistently. A surrogate key column name might be _id.
Data Source
system catalog only
SQL Query
with pk_columns AS (select
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='p'
and cardinality(o.conkey)=1),
pk_columns_pattern AS (select replace(lower(column_name), lower(table_name), 'table') as pattern
from pk_columns)
select pattern, count(*) as cnt
from pk_columns_pattern
group by pattern
order by Count(*) desc, pattern;
Collections
This query belongs to the following collections:
Name
Description
Find problems about names
A 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.
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
Categories
This query is classified under the following categories:
Name
Description
Comfortability of data management
Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
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.
Further reading and related materials:
Reference
Smell "Identifier construction rules": Abebe, S.L., Haiduc, S., Tonella, P. and Marcus, A., 2011, September. The effect of lexicon bad smells on concept location in source code. In 2011 IEEE 11th International Working Conference on Source Code Analysis and Manipulation (pp. 125-134). IEEE.