Find column names in case of which the same table has another column (with more specific name) that name contains the column name in the end or in the beginning. For instance, a base table has columns parent and root_parent and the former col-umn name is too generic, i.e., it should be more specific.
Notes
The query assumes that snake_case is used in the names. The query excludes too generic column names "id", "code", and "kood".
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH columns AS (SELECT nspname AS table_schema, relname AS table_name,
CASE WHEN relkind='r' THEN 'BASE TABLE'
WHEN relkind='v' THEN 'VIEW'
WHEN relkind='m' THEN 'MATERIALIZED VIEW'
WHEN relkind='f' THEN 'FOREIGN TABLE'
WHEN relkind='p' THEN 'PARTITIONED TABLE'
END AS table_type,
attname AS column_name,
pg_type.typname AS column_type,
domain_type.typname AS column_domain_type
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
INNER JOIN pg_type ON pg_attribute.atttypid =pg_type.oid
LEFT JOIN pg_type AS domain_type ON domain_type.oid=pg_type.typbasetype
WHERE attnum>=1
AND relkind IN ('r','v','m','f','p')
AND nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT c.table_schema, c.table_type, c.table_name,
c.column_name AS suspected_name, c.column_type AS c_type, c.column_domain_type AS c_domain_type,
c2.column_name AS more_specific_name, c2.column_type AS c2_type, c2.column_domain_type AS c2_domain_type
FROM columns AS c INNER JOIN columns AS c2 USING (table_schema, table_name)
WHERE c.column_name<>c2.column_name
AND (c2.column_name ILIKE '%\_' || c.column_name
OR c2.column_name ILIKE c.column_name || '\_%')
AND c.column_name NOT IN ('id','code','kood')
ORDER BY table_type, table_schema, table_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 .
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
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Naming
Queries of this category provide information about the style of naming.
Further reading and related materials:
Reference
Smell "Inconsistent identifier use": Abebe, S.L., Haiduc, S., Tonella, P., Marcus, A., 2011. 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.