Query goal: | Find simple check constraints (involve one column) that are associated with a base table column instead of the domain of the column. Common checks of data in columns that share the same domain should be described at the level of domain not at the level of columns. |
Notes about the query: | In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Medium (Medium number of false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Make sure that the check that is directly associated with a column applies to all the columns that have the same domain as the column in question. If it is the case, then drop the check constraint that is associated directly with a column and associate it with the domain of the column. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH simple_checks AS (SELECT (SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema, c.relname as table_name, a.attname AS column_name, o.conname, substring(pg_get_constraintdef(o.oid),7) AS check_clause FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE WHERE cardinality(o.conkey)=1 AND o.contype = 'c' AND c.relkind = 'r'), simple_checks_agg AS (SELECT table_schema, table_name, column_name, string_agg(check_clause,';<br>' ORDER BY conname) AS column_checks FROM simple_checks GROUP BY table_schema, table_name, column_name), column_domain_usage AS (SELECT cdu.domain_schema, cdu.domain_name, cdu.table_schema, cdu.table_name, cdu.column_name FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name) WHERE t.table_type='BASE TABLE' AND cdu.table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND catalog_name IS NOT NULL AND schema_name IS NOT NULL)) SELECT cdu.domain_schema, domain_name, sc.table_schema, sc.table_name, sc.column_name, sc.column_checks FROM simple_checks_agg sc INNER JOIN column_domain_usage cdu USING (table_schema, table_name, column_name) ORDER BY cdu.domain_schema, domain_name, sc.table_schema, sc.table_name, sc.column_name; |
Collection name | Collection description |
---|---|
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 . |
Category name | Category description |
---|---|
CHECK constraints | Queries of this category provide information about CHECK constraints. |
Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
Domains | Queries of this category provide information about reusable specifications of column properties. |