Find base table and foreign table columns with the Boolean type that has a CHECK constraint that involves only this column. Avoid unnecessary CHECK constraints. The Boolean type contains only two values and there is nothing to check. By creating a check that determines that possible values in the column are TRUE and FALSE, one duplicates the attribute constraint (column has a type). This is a form of duplication.
Notes
The query finds CHECK constraints that are associated with a base table directly as well as CHECK constraints that are associated with domains that are used to define at least one column. The query does not find CHECK constraints of domains that are not associated with any table.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH boolean_domains_with_checks AS (
SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, cc.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)
INNER JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name)
WHERE t.table_type IN ('BASE TABLE' , 'FOREIGN')
AND d.data_type= 'boolean'
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)),
boolean_columns_with_checks AS (SELECT
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema,
c.relname as table_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE' ELSE 'FOREIGN' END AS table_type,
a.attname 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
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
INNER JOIN pg_type t ON a.atttypid=t.oid
WHERE cardinality(o.conkey)=1
AND o.contype = 'c'
AND t.typname='bool'
AND c.relkind IN ('r','f')
UNION SELECT table_schema, table_name, table_type, column_name, check_clause
FROM boolean_domains_with_checks)
SELECT table_schema, table_name, table_type, column_name, consrc
FROM boolean_columns_with_checks
ORDER BY table_schema, table_name;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
WITH table_checks AS (SELECT
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema,
c.relname as table_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE' ELSE 'FOREIGN' END AS table_type,
o.conname
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
INNER JOIN pg_type t ON a.atttypid=t.oid
WHERE cardinality(o.conkey)=1 AND o.contype = 'c' AND o.conrelid in (SELECT oid FROM pg_class c WHERE c.relkind IN ('r','f'))
AND t.typname='bool')
SELECT format('ALTER %4$s TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', table_schema, table_name, conname, CASE WHEN table_type='FOREIGN' THEN table_type END) AS statements
FROM table_checks
ORDER BY table_schema, table_name;
Drop the CHECK constraint that is directly associated with a base table.
WITH domains AS (
SELECT cdu.table_schema, cdu.table_name, cdu.domain_schema, cdu.domain_name, dc.constraint_name, (SELECT data_type FROM INFORMATION_SCHEMA.domains AS d
WHERE d.domain_schema=dc.domain_schema AND d.domain_name=dc.domain_name) AS data_type,
cc.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 IN ('BASE TABLE', 'FOREIGN') 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)),
boolean_domains AS (
SELECT domain_schema, domain_name, constraint_name, data_type
FROM domains
WHERE data_type = 'boolean')
SELECT format('ALTER DOMAIN %1$I.%2$I DROP CONSTRAINT %3$I;', domain_schema, domain_name, constraint_name) AS statements
FROM boolean_domains
ORDER BY domain_schema, domain_name;
Drop the CHECK constraint that is associated with a domain.
Collections
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
Description
Boolean data
Queries of this category provide information about truth-values data that is kept in the database.
CHECK constraints
Queries of this category provide information about CHECK constraints.
Duplication of implementation elements
Queries of this catergory provide information about the duplication of the database objects.