Goal Find CHECK constraints that use precise comparison (= or <>) with a regular expression or LIKE pattern.
Notes The query considers table and column check constraints. The quer considers domain check constraints and simple table check constraints, i.e., constraints that cover one column.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Low Many false-positive results
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
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 cardinality(o.conkey)=1  
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 ~'[[:space:]](<>|=)[[:space:]]''[(]*.*(\^{0,1}.*(\^|\\|\[|\]|\{|\}|\*|[{]|[}]|:upper:|:alnum:|:alpha:|:blank:|:space:|:digit:){1}.*\${0,1}|[%])'
ORDER BY schema, type, name;

Collections

This query belongs to the following collections:

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 .

NameDescription
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

This query is classified under the following categories:

CHECK constraints

Queries of this category provide information about CHECK constraints.

Regular expressions

Queries of this catergory provide information about the use of regular expressions.

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
Regular expressionsQueries of this catergory provide information about the use of regular expressions.