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)
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:
Name
Description
Naming
Queries of this category provide information about the style of naming.