Goal Find exclude constraints that implement a simple UNIQUE constraint. The checking might be slower compared to UNIQUE constraint.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT License
Fixing Suggestion Drop the exclude constraint and create UNIQUE constraint instead.
Data Source system catalog only
SQL Query
with excl as (select 
o.conname, 
(select nspname from pg_namespace where oid=c.relnamespace) as excl_schema,
c.relname as excl_table, 
c.oid as excl_table_oid,
ix.indkey AS excl_col,
o.conexclop AS excl_op,
pg_get_indexdef(o.conindid) as index_def
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_index ix on ix.indexrelid=o.conindid
where o.contype in ('x') ),
excl_unnest as (select conname, excl_schema, excl_table, excl_table_oid, excl_col, excl_col_num, ordin_col, index_def, excl_op
from excl, unnest(excl.excl_col) with ordinality as k(excl_col_num, ordin_col)),
excl_with_names as (select conname, excl_schema, excl_table,  array_agg(a_key.attname order by ordin_col) as excl_col, index_def, excl_op 
from excl_unnest k left join pg_attribute a_key on k.excl_col_num = a_key.attnum and k.excl_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, excl_schema, excl_table, index_def, excl_op),
excl_unnest_op as (select conname, excl_schema, excl_table, excl_col, index_def, excl_op, excl_op_num, ordin_op
from excl_with_names, unnest(excl_with_names.excl_op) with ordinality as k(excl_op_num, ordin_op)),
excl_with_names_op as (select conname, excl_schema, excl_table, excl_col, index_def, array_agg(op.oprname order by ordin_op) as excl_op, count(distinct op.oprname) as nr_of_distinct_operators
from excl_unnest_op k inner join pg_operator op on k.excl_op_num=op.oid
group by conname, excl_schema, excl_table, excl_col, index_def)
select conname, excl_schema, excl_table, excl_col, excl_op, index_def
from excl_with_names_op
where nr_of_distinct_operators=1
and excl_op[1] = '='
and index_def not like '% WHERE %' 
and index_def not like '%(upper(%'
and index_def not like '%(lower(%'  
order by excl_schema, excl_table, excl_col;

Collections

This query belongs to the following collections:

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:

NameDescription
PerformanceQueries of this category provide information about indexes in a database.
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.