Goal Find non-foreign key base table columns that have the same name but a different set of check constraints. The use of constraints should be consistent and all the necessary constraints must be enforced. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)
Notes The query assumes that columns with the same name contain data that must follow the same rules. This may not always be the case. 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. Empty set is also a set and thus the query also considers cases where the column has no constraints. The query considers only check constraints that involve one column. Foreign key columns are excluded to reduce the number of false positives because one should not duplicate constraints of the candidate key to the foreign key column that references it. Regexp_replace function is used to remove brackets from the beginning and end of the boolean expression, if both brackets exist.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Fixing Suggestion Drop the unnecessary constraints. If different columns contain data that must follow the same rules, then try to implement the rules in the same way, i.e., use the same Boolean expressions in case of constraints.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH simple_check_constraints AS (
select 
n.nspname as table_schema,
c.relname as table_name,
a.attname as column_name,
o.conname as constraint_name,
substring(pg_get_constraintdef(o.oid)::text, 9,length(pg_get_constraintdef(o.oid))-10) AS check_clause
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on o.connamespace=n.oid
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
where o.contype = 'c' AND cardinality(o.conkey)=1),

fk as (select 
o.conname,
(select nspname from pg_namespace where oid=m.relnamespace) as key_schema,
m.relname as key_table, 
m.oid as key_table_oid,
o.conkey AS key_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class m on m.oid = o.conrelid
where o.contype='f'  and o.conrelid in (select oid from pg_class c where c.relkind = 'r')),
fk_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin
from fk, unnest(fk.key_col) with ordinality as k(key_col_num, ordin)),
fk_with_names as (select conname, key_schema, key_table, a_key.attname as key_col
from fk_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false),

chk_constraints AS (SELECT table_schema, table_name, column_name, check_clause, constraint_name
FROM simple_check_constraints
UNION SELECT cdu.table_schema, cdu.table_name, cdu.column_name, cc.check_clause, cc.constraint_name
FROM ((INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN
INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)) INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc
USING (domain_schema, domain_name)) INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc
USING (constraint_catalog, constraint_schema, constraint_name)
WHERE t.table_type='BASE TABLE' AND cc.check_clause NOT LIKE '%IS NOT NULL'  AND 
cdu.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)),

chk_all AS (
SELECT table_schema, table_name, column_name, check_clause, constraint_name
FROM chk_constraints
UNION SELECT table_schema, table_name, column_name, 'No check' AS check_clause, '' AS constraint_name
FROM INFORMATION_SCHEMA.columns AS c INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
WHERE table_type='BASE TABLE'
AND 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)
AND NOT EXISTS (SELECT *
FROM chk_constraints AS scc
WHERE scc.table_schema=c.table_schema
AND scc.table_name=c.table_name
AND scc.column_name=c.column_name)),

chk_all_processed AS (SELECT table_schema, table_name, column_name, regexp_replace(check_clause, '^\((.+)\)$', '\1') AS check_clause, constraint_name
FROM chk_all),

chk_constraints_generalized AS (SELECT table_schema, table_name, column_name, constraint_name,
replace(lower(check_clause), lower (column_name), 'value') AS check_clause
FROM chk_all_processed AS c
WHERE NOT EXISTS (SELECT *
FROM fk_with_names AS f
WHERE f.key_schema=c.table_schema
AND f.key_table=c.table_name
AND f.key_col=c.column_name)),

chk_constraints_agg AS (SELECT table_schema, table_name, column_name, 
string_agg(check_clause,';' ORDER BY check_clause) AS check_clause
FROM chk_constraints_generalized
GROUP BY table_schema, table_name, column_name)

SELECT column_name, Count(DISTINCT check_clause) AS cnt_different,
Count(check_clause) AS cnt_total,
string_agg(DISTINCT check_clause, ';
' ORDER BY check_clause) AS consrc_different, string_agg('' || table_schema || '.' || table_name || ' ' || check_clause, ';
' ORDER BY check_clause) AS consrc_all FROM chk_constraints_agg GROUP BY column_name HAVING Count(DISTINCT check_clause)>1 ORDER BY Count(DISTINCT check_clause) DESC;
Collections

This query belongs to the following collections:

NameDescription
Find problems about integrity constraintsA selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
Find problems automaticallyQueries, 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:

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
NamingQueries of this category provide information about the style of naming.
Validity and completenessQueries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).

Further reading and related materials:

Reference
The corresponding code smell in case of cleaning code is "G11: Inconsistency". (Robert C. Martin, Clean Code)
Smell "Overloaded attribute names": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018).