Perhaps primary key columns could be renamed

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;

Query goal:	Find the names of simple primary key columns that name does not follow the pattern _id or _code but it is quite similar.
Notes about the query:	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.
Query type:	Problem detection (Each row in the result could represent a flaw in the design)
Query reliability:	Low (Many false-positive results)
Query license:	MIT License
Data source:	system catalog only
SQL query:	Click on query to copy it 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;

Perhaps primary key columns could be renamed

Categories where the query belongs to

Reference materials for further reading