The list of all the queries

CHECKs are associated with a column instead of the domain of the column

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;

Categories where the query belongs to

Category nameCategory description
CHECK constraintsQueries of this category provide information about CHECK constraints.
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
DomainsQueries of this category provide information about reusable specifications of column properties.

The list of all the queries