The list of all the queries

Only one value permitted in a non-inherited base table or a foreign table column (based on check constraints)

Query goal: Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. Exclude columns that are inherited from a supertable because the constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable.
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 constraints or drop the column.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH columns_with_domains_chk AS (
SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_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, 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_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 schema_name IS NOT NULL)),

simple_chk 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,
t.typname AS type_name,
substring(pg_get_constraintdef(o.oid),7) AS check_clause,
o.conname AS constraint_name
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 c.relkind IN ('r','f')
UNION SELECT table_schema, table_name, table_type, column_name, data_type, check_clause, constraint_name
FROM columns_with_domains_chk),

inheritance AS (SELECT pn.nspname AS parent_schema, p.relname AS parent_table, pc.nspname AS child_schema,  c.relname AS child_table,
c.relispartition AS child_is_partition
FROM pg_inherits pi INNER JOIN pg_class p ON pi.inhparent=p.oid
INNER JOIN pg_namespace pn ON p.relnamespace=pn.oid
INNER JOIN pg_authid AS ap ON pn.nspowner=ap.oid
INNER JOIN pg_class c ON pi.inhrelid=c.oid
INNER JOIN pg_namespace pc ON c.relnamespace=pc.oid
INNER JOIN pg_authid AS ac ON pc.nspowner=ac.oid
WHERE (pn.nspname='public' OR ap.rolname<>'postgres') 
AND (pc.nspname='public' OR ac.rolname<>'postgres'))


SELECT  table_schema, table_name, table_type, column_name, type_name, constraint_name, check_clause, is_nullable
FROM simple_chk AS ch INNER JOIN INFORMATION_SCHEMA.columns AS c USING(table_schema, table_name, column_name)
WHERE check_clause~'[[:space:]](=)[[:space:]]' 
AND check_clause!~*'[[:space:]]any[[:space:]][\(]+array\[' 
AND check_clause!~*'(<|>|<>|>=|<=)' 
AND check_clause!~*'(left|right|substring|trim|length)' 
AND check_clause!~*'[[:space:]](and|or)[[:space:]]' 
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+' || column_name || '[)]+')
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+value[)]+')
AND check_clause !~* ( '(current_date|current_timestamp|localtimestamp|now()|clock_timestamp|statement_timestamp|transaction_timestamp)')

AND NOT EXISTS (SELECT *
FROM inheritance AS i INNER JOIN INFORMATION_SCHEMA.columns AS ic ON i.parent_schema=ic.table_schema AND i.parent_table=ic.table_name
WHERE ch.table_schema=i.child_schema 
AND ch.table_name=i.child_table
AND ch.column_name=ic.column_name
)

ORDER BY table_schema, table_name, column_name;

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

SQL queryDescription
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 check_clause,
o.conname AS constraint_name
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
WHERE cardinality(o.conkey)=1 
AND o.contype = 'c' 
AND c.relkind IN ('r','f')),
inheritance AS (SELECT pn.nspname AS parent_schema, p.relname AS parent_table, pc.nspname AS child_schema,  c.relname AS child_table,
c.relispartition AS child_is_partition
FROM pg_inherits pi INNER JOIN pg_class p ON pi.inhparent=p.oid
INNER JOIN pg_namespace pn ON p.relnamespace=pn.oid
INNER JOIN pg_authid AS ap ON pn.nspowner=ap.oid
INNER JOIN pg_class c ON pi.inhrelid=c.oid
INNER JOIN pg_namespace pc ON c.relnamespace=pc.oid
INNER JOIN pg_authid AS ac ON pc.nspowner=ac.oid
WHERE (pn.nspname='public' OR ap.rolname<>'postgres') 
AND (pc.nspname='public' OR ac.rolname<>'postgres'))
SELECT format('ALTER %4$s TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', table_schema, table_name, constraint_name, CASE WHEN table_type='FOREIGN' THEN table_type END) AS statements
FROM checks AS ch
WHERE check_clause~'[[:space:]](=)[[:space:]]' 
AND check_clause!~*'[[:space:]]any[[:space:]][\(]+array\[' 
AND check_clause!~*'(<|>|<>|>=|<=)' 
AND check_clause!~*'[[:space:]](and|or)[[:space:]]' 
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+' || column_name || '[)]+')
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+value[)]+')
AND check_clause !~* ( '(current_date|current_timestamp|localtimestamp|now()|clock_timestamp|statement_timestamp|transaction_timestamp)')
AND NOT EXISTS (SELECT *
FROM inheritance AS i INNER JOIN INFORMATION_SCHEMA.columns AS ic ON i.parent_schema=ic.table_schema AND i.parent_table=ic.table_name
WHERE ch.table_schema=i.child_schema 
AND ch.table_name=i.child_table
AND ch.column_name=ic.column_name
)
ORDER BY statements;
Drop the constraint that is directly associated with the table.
WITH checks AS (SELECT cdu.table_schema, cdu.table_name, cdu.domain_schema, cdu.domain_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_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 schema_name IS NOT NULL)),

inheritance AS (SELECT pn.nspname AS parent_schema, p.relname AS parent_table, pc.nspname AS child_schema,  c.relname AS child_table,
c.relispartition AS child_is_partition
FROM pg_inherits pi INNER JOIN pg_class p ON pi.inhparent=p.oid
INNER JOIN pg_namespace pn ON p.relnamespace=pn.oid
INNER JOIN pg_authid AS ap ON pn.nspowner=ap.oid
INNER JOIN pg_class c ON pi.inhrelid=c.oid
INNER JOIN pg_namespace pc ON c.relnamespace=pc.oid
INNER JOIN pg_authid AS ac ON pc.nspowner=ac.oid
WHERE (pn.nspname='public' OR ap.rolname<>'postgres') 
AND (pc.nspname='public' OR ac.rolname<>'postgres'))


SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I DROP CONSTRAINT %3$I;', domain_schema, domain_name, constraint_name) AS statements
FROM checks AS ch
WHERE check_clause~'[[:space:]](=)[[:space:]]' 
AND check_clause!~*'[[:space:]]any[[:space:]][\(]+array\[' 
AND check_clause!~*'(<|>|<>|>=|<=)' 
AND check_clause!~*'[[:space:]](and|or)[[:space:]]' 
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+' || column_name || '[)]+')
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+value[)]+')
AND check_clause !~* ( '(current_date|current_timestamp|localtimestamp|now()|clock_timestamp|statement_timestamp|transaction_timestamp)')


AND NOT EXISTS (SELECT *
FROM inheritance AS i INNER JOIN INFORMATION_SCHEMA.columns AS ic ON i.parent_schema=ic.table_schema AND i.parent_table=ic.table_name
WHERE ch.table_schema=i.child_schema 
AND ch.table_name=i.child_table
AND ch.column_name=ic.column_name
)


ORDER BY statements;
Drop the constraint that is associated with the domain.
WITH columns_with_domains_chk AS (
SELECT cdu.table_schema, cdu.table_name, t.table_type, 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_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 schema_name IS NOT NULL)),

simple_chk 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 check_clause,
o.conname AS constraint_name
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
WHERE cardinality(o.conkey)=1 
AND o.contype = 'c' 
AND c.relkind IN ('r','f')
UNION SELECT table_schema, table_name, table_type, column_name, check_clause, constraint_name
FROM columns_with_domains_chk),

inheritance AS (SELECT pn.nspname AS parent_schema, p.relname AS parent_table, pc.nspname AS child_schema,  c.relname AS child_table,
c.relispartition AS child_is_partition
FROM pg_inherits pi INNER JOIN pg_class p ON pi.inhparent=p.oid
INNER JOIN pg_namespace pn ON p.relnamespace=pn.oid
INNER JOIN pg_authid AS ap ON pn.nspowner=ap.oid
INNER JOIN pg_class c ON pi.inhrelid=c.oid
INNER JOIN pg_namespace pc ON c.relnamespace=pc.oid
INNER JOIN pg_authid AS ac ON pc.nspowner=ac.oid
WHERE (pn.nspname='public' OR ap.rolname<>'postgres') 
AND (pc.nspname='public' OR ac.rolname<>'postgres'))

SELECT DISTINCT format('ALTER %4$s TABLE %1$I.%2$I DROP COLUMN %3$I;', table_schema, table_name, column_name, CASE WHEN table_type='FOREIGN' THEN table_type END) AS statements
FROM simple_chk AS ch
WHERE check_clause~'[[:space:]](=)[[:space:]]' 
AND check_clause!~*'[[:space:]]any[[:space:]][\(]+array\[' 
AND check_clause!~*'(<|>|<>|>=|<=)' 
AND check_clause!~*'[[:space:]](and|or)[[:space:]]' 
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+' || column_name || '[)]+')
AND check_clause !~* ( '([[:alnum:]]|_)+[(]+value[)]+')
AND check_clause !~* ( '(current_date|current_timestamp|localtimestamp|now()|clock_timestamp|statement_timestamp|transaction_timestamp)')

AND NOT EXISTS (SELECT *
FROM inheritance AS i INNER JOIN INFORMATION_SCHEMA.columns AS ic ON i.parent_schema=ic.table_schema AND i.parent_table=ic.table_name
WHERE ch.table_schema=i.child_schema 
AND ch.table_name=i.child_table
AND ch.column_name=ic.column_name
)

ORDER BY statements;
Drop the column.

Collections where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
CHECK constraintsQueries of this category provide information about CHECK constraints.
Table inheritanceQueries of this category provide information about the inheritance between base tables.
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).

The list of all the queries