Find the names of simple primary key columns that name does not follow the pattern _id or _code but it is quite similar.
Notes
The query finds tables with simple primary keys where the Levenshtein distance between the primary key column name (without id/code/kood prefix and suffix) and table name is between one and three. The query uses a function from the fuzzystrmatch extension.
Type
Problem detection (Each row in the result could represent a flaw in the design)
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
with pk_columns 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 rolname<>'postgres')
and o.contype='p' and cardinality(o.conkey)=1),
pk_columns_pattern AS (select table_schema, table_name, column_name,
regexp_replace(column_name, '[_]*(id|kood|code)[_]*', '', 'g') as pattern
from pk_columns
where column_name!~*'^(id|code|kood)$')
select table_schema, table_name, column_name as actual_primary_key_column_name,
case when column_name~*'[_]*(id)[_]*' then table_name || '_id'
when column_name~*'[_]*(code)[_]*' then table_name || '_code'
when column_name~*'[_]*(kood)[_]*' then table_name || '_kood' end as suggested_primary_key_column_name
from pk_columns_pattern
where levenshtein(table_name,pattern) between 1 and 3
order by table_schema, table_name;
DROP EXTENSION IF EXISTS fuzzystrmatch;
Categories
This query is classified under the following categories:
Name
Description
Naming
Queries of this category provide information about the style of naming.