Perhaps an overcomplicated constraint expression that compares the result of a Boolean expression with a Boolean value

Query goal:

Find table and domain CHECK constraints that compare the result of a Boolean expression with a Boolean value. If you can choose between two logically equivalent Boolean expressions choose the more simple expression.

Query type:

Problem detection (Each row in the result could represent a flaw in the design)

Query reliability:

Low (Many false-positive results)

Query license:

MIT License

Fixing suggestion:

Drop and re-create the constraint. For instance, instead of writing CHECK ((price>0)=TRUE) write CHECK (price>0)

Data source:

INFORMATION_SCHEMA+system catalog

SQL query:

Click on query to copy it

WITH expressions AS (select 
n.nspname as schema,
c.relname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'TABLE CHECK' AS type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
where o.contype ='c'  
and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
union select n.nspname as schema,
t.typname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'DOMAIN CHECK' AS type
from pg_constraint o inner join pg_type t on t.oid = o.contypid
inner join pg_namespace n on n.oid=t.typnamespace
where o.contype ='c' and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null))
SELECT schema, name AS object_identifier, type AS object_type, expression AS suspected_expression
FROM expressions
WHERE expression ~*'is[[:space:]]+(not[[:space:]]*)*(true|false|unknown)'
OR expression ~*'[)][[:space:]]*(=|>|<|>=|<=)[[:space:]]*(true|false|unknown)'
ORDER BY schema, type, name;

Collections where the query belongs to

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

Collection name

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

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.
Missing data	Queries of this category provide information about missing data (NULLs) in a database.

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.

Missing data

Queries of this category provide information about missing data (NULLs) in a database.