Goal Find the names of columns where the name of the column contains a part of the name of the data type of the column. For instance, the query finds columns, were the name contains fragments integer_ or _integer.
Notes The query excludes the columns that names have in addtion to other components the components "date", "time", or "timestamp". However, the query detects the columns that name is exactly "date", "time", or "timestamp".
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Low (Many false-positive results)
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA only
SQL Query 
WITH columns AS (SELECT table_schema, table_name, table_type, column_name, ordinal_position, data_type,
regexp_split_to_array(data_type, ' ') AS data_type_part
FROM INFORMATION_SCHEMA.columns INNER JOIN INFORMATION_SCHEMA.tables USING (table_schema, table_name)
WHERE 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)),
data_type_names_split AS (SELECT table_schema, table_name, table_type, Unnest(data_type_part) AS data_type_part, data_type, column_name, ordinal_position
FROM columns)
SELECT table_schema, table_name, table_type, data_type, column_name
FROM data_type_names_split
WHERE column_name ILIKE data_type_part
OR ((data_type_part!~'(time|date)') 
AND (column_name ILIKE '%\_' || data_type_part || '%'
OR column_name ILIKE '%' || data_type_part || '\_%'))
ORDER BY table_schema, table_name, ordinal_position;

Categories

This query is classified under the following categories:

Naming

Queries of this category provide information about the style of naming.

NameDescription
NamingQueries of this category provide information about the style of naming.