Goal Find exclude constraints that are based on the gist index type although the default b-tree index type would have been enough.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
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,
o.conkey AS excl_col,
o.conexclop AS excl_op,
pg_get_constraintdef(o.oid) as const_def
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('x') ),
excl_unnest as (select conname, excl_schema, excl_table, excl_table_oid, excl_col, excl_col_num, ordin_col, const_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, const_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, const_def, excl_op),
excl_unnest_op as (select conname, excl_schema, excl_table, excl_col, const_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, const_def,  array_agg(op.oprname order by ordin_op) as excl_op, array_agg(DISTINCT op.oprname order by oprname) as excl_op_distinct
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, const_def)
select conname, excl_schema, excl_table, excl_col, excl_op, const_def
from excl_with_names_op
where cardinality(excl_op_distinct)=1
and excl_op_distinct[1]='='
and const_def~'USING gist'
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
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.