Query goal: | 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 about the query: | 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. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
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; |
Collection name | Collection 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 . |
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. |
Inconsistencies | Queries of this catergory provide information about inconsistencies of solving the same problem in different places. |
Reference |
---|
The corresponding code smell in case of cleaning code is "G11: Inconsistency". (Robert C. Martin, Clean Code) |