Goal This query identifies exclusion constraints that apply the upper() function to a column. In PostgreSQL, it is a strong community convention to standardize on the lower() function for case-insensitive constraints, indexing, and comparisons. Enforcing this convention ensures consistency across the schema and prevents logical or performance issues (for example, if a developer queries or validates data using lower(col) but the underlying exclusion constraint's index is built on upper(col)).
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 (opens in new tab)
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
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 const_def LIKE '%(upper(%'
order by excl_schema, excl_table, excl_col;

Fixing Suggestion

To resolve this issue and align with database conventions, drop the existing exclusion constraint using the ALTER TABLE ... DROP CONSTRAINT statement. Then, recreate the exclusion constraint applying the lower() function instead of upper() to the relevant columns. Ensure that any application-level validation or queries referencing these columns are updated to match.

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:

Inconsistencies

Queries of this catergory provide information about inconsistencies of solving the same problem in different places.

Performance

Queries of this category provide information about indexes in a database.

NameDescription
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
PerformanceQueries of this category provide information about indexes in a database.