This query generates a frequency distribution of semantically weak column identifiers within the schema. It aggregates the occurrences of generic names (e.g., id, type, data) to quantify their prevalence across the database. This statistical view serves as a prioritization tool for refactoring, highlighting the most ubiquitous violations of naming conventions that contribute to schema ambiguity.
Notes
The query considers both names in English and in Estonian. The query excludes some names like database, data_type, and additional_info.
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' AS suspected_object_type, column_name AS suspected_name, data_type
FROM information_schema.columns INNER JOIN information_schema.tables USING (table_schema, table_name)
UNION SELECT nspname, relname, 'MATERIALIZED VIEW COLUMN' AS type, attname, typname
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
WHERE relkind = 'm' AND attnum>=1)
SELECT translate(suspected_name,'0123456789_','') AS suspected_name, Count(*) AS nr_of_occurrences
FROM columns
WHERE (suspected_name~*'(andme(?!(baas|[_]*tyyp))|(?'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
GROUP BY translate(suspected_name,'0123456789_','')
ORDER BY Count(*) DESC, suspected_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
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.