This query identifies base table columns that unnecessarily include the table name. It searches for columns that are not part of a primary or foreign key and contain the name of their parent table. To avoid flagging legitimate naming conventions, it explicitly excludes a list of generic column names (e.g., name, description, nimi, kommentaar) where prefixing with the table name is considered good practice for improving clarity in queries.
Notes
In the query result (b) tag is used to mark the table name within the column name. The query considers both column names in English and Estonian.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Rename the columns. For instance, instead of having column person_surname in the table Person use the name surname. Use a consistent style of naming.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
WITH primary_key_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name
from (select
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table,
c.oid as target_table_oid,
unnest(o.conkey) AS target_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('p')) t
inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false),
fk_columns AS (
select target_schema as table_schema, target_table as table_name, a.attname as column_name
from (select
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table,
c.oid as target_table_oid,
unnest(o.conkey) AS target_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'f') t
inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false
)
SELECT table_schema, table_name, regexp_replace(column_name, table_name, '' || table_name || '','g') AS column_name
FROM INFORMATION_SCHEMA.columns AS c
WHERE column_name ~* ('^' || table_name || '.{0,1}[_]+(?!(nimi|nimetus|kommentaar|kirjeldus|staatus|seisund|olek|tyyp|tüüp|tekst|name|comment|description|explanation|state|status|type|text))')
AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND
table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND
NOT EXISTS (SELECT 1 FROM primary_key_columns AS pk WHERE pk.table_schema=c.table_schema AND pk.table_name=c.table_name AND pk.column_name=c.column_name) AND
NOT EXISTS (SELECT 1 FROM fk_columns AS fk WHERE fk.table_schema=c.table_schema AND fk.table_name=c.table_name AND fk.column_name=c.column_name)
ORDER BY table_schema, table_name, column_name;
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 automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
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.
Naming
Queries of this category provide information about the style of naming.
Further reading and related materials:
Reference
The corresponding code problem in case of cleaning code is "Don’t Add Gratuitous Context". (Robert C. Martin, Clean Code)