BOOLEAN base table and foreign table columns with a CHECK constraint that involves olnly this column

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 query	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.

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;

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;

Query goal:	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 about the query:	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.
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:	Drop the CHECK constraint.
Data source:	INFORMATION_SCHEMA+system catalog
SQL query:	Click on query to copy it 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;

Category name	Category 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.

BOOLEAN base table and foreign table columns with a CHECK constraint that involves olnly this column

SQL statements for generating SQL statements that help us to fix the problem

Collections where the query belongs to

Categories where the query belongs to

Reference materials for further reading