Goal Find multicolumn CHECK constraints. Such constraints must be associated directly with a base table, i.e., these cannot be associated with a domain. Enforce as much data integrity as possible at the database level and prefer declarative constraints to a trigger.
Notes In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser.
Type General Overview of some aspect of the database.
License MIT (opens in new tab)
Data Source system catalog only
SQL Query
with ck as (select 
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table, 
c.oid as target_table_oid,
unnest(o.conkey) AS target_col,
cardinality(o.conkey) AS number_of_columns,
substring(pg_get_constraintdef(o.oid),7) as consrc
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'c' and cardinality(o.conkey)>1),
ck_grouped as (
select ck.conname, ck.target_schema, ck.target_table, string_agg(a_target.attname, ',<br>'  order by  a_target.attnum) as target_col, ck.consrc, ck.number_of_columns
from ck inner join pg_attribute a_target on ck.target_col = a_target.attnum and ck.target_table_oid = a_target.attrelid and a_target.attisdropped = false
group by ck.conname, ck.target_schema, ck.target_table, ck.consrc, ck.number_of_columns)
select conname, target_schema as schema, target_table as table, target_col as columns, number_of_columns, consrc AS check_clause
from ck_grouped
order by target_schema, target_table, number_of_columns DESC;

Collections

This query belongs to the following collections:

Find problems about integrity constraints

A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview

Find problems by overview

Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

NameDescription
Find problems about integrity constraintsA selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Categories

This query is classified under the following categories:

CHECK constraints

Queries of this category provide information about CHECK constraints.

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.