Find patterns of the Boolean expressions of simple CHECK constraints (involve only one column). Do not solve the same task in different places differently. The same rule could be implemented with CHECK constraints that have different Boolean expressions. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)
Notes
The query considers only CHECK constraints that are associated with exactly one column. The query also considers constraints that are associated with a column through a domain.
Type
General (Overview of some aspect of the database.)
WITH checks AS (SELECT
o.conname AS constraint_name,
c.relname AS table_name,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS column_name,
substring(pg_get_constraintdef(o.oid),7) as consrc
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
WHERE o.contype = 'c' AND cardinality(o.conkey)=1
UNION ALL SELECT cc.constraint_name, cdu.table_name, cdu.column_name, regexp_replace(cc.check_clause, '^([(])(.*)([)])$','\2') AS check_clause
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 catalog_name IS NOT NULL AND schema_name IS NOT NULL)),
checks_pattern AS (
select replace(consrc,'('|| column_name,'(VALUE') AS pattern
FROM checks)
SELECT pattern, Count(*) AS number_of_occurrences
FROM checks_pattern
GROUP BY pattern
ORDER BY Count(*) DESC;
Collections
This query belongs to the following collections:
Name
Description
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories
This query is classified under the following categories:
Name
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.
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Further reading and related materials:
Reference
The corresponding code smell in case of cleaning code is "G11: Inconsistency". (Robert C. Martin, Clean Code)