Find unique index (not associated with a constraint) columns with the names like id, identifikaator, code, kood, number, etc. The names could have underscores as the prefix or suffix. These are too generic names.
Notes
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)
SELECT
n.nspname AS table_schema,
c2.relname AS table_name,
a.attname AS column_name
FROM
pg_catalog.pg_class AS c INNER JOIN pg_catalog.pg_index AS i ON i.indexrelid = c.oid
INNER JOIN pg_catalog.pg_class AS c2 ON i.indrelid = c2.oid
INNER JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid
INNER JOIN pg_catalog.pg_authid AS u ON n.nspowner = u.oid
INNER JOIN pg_catalog.pg_attribute AS a ON a.attrelid = c.oid
WHERE c.relkind = 'i'
AND i.indisunique=TRUE
AND a.attnum>=1
AND a.attisdropped = FALSE
AND (n.nspname = 'public'
OR u.rolname <> 'postgres')
AND NOT EXISTS (SELECT * FROM pg_catalog.pg_constraint AS o WHERE o.conindid=i.indexrelid AND o.contype IN ('u','p'))
AND a.attname ~*'^[^[:alpha:]]*(id|kood|identifikaator|identifier|code|number)[^[:alpha:]]*$'
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
Naming
Queries of this category provide information about the style of naming.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.
Further reading and related materials:
Reference
The corresponding code problem in case of cleaning code is "Add Meaningful Context". (Robert C. Martin, Clean Code)
This is a part of an antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 4: ID Required.
The corresponding code smells in case of cleaning code are "N1: Choose Descriptive Names" and "N4: Unambiguous Names". (Robert C. Martin, Clean Code)