Goal This query identifies unique constraints and indexes configured with the NULLS NOT DISTINCT property where all involved columns are mandatory (NOT NULL). Using this modifier on strictly mandatory columns is completely redundant. Since the columns cannot contain NULL values in the first place, the special null-handling logic serves no purpose and only unnecessarily clutters the database schema.
Notes Refers to the column pg_index.indnullsnotdistinct and thus works starting from PostgreSQL 15.
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 INFORMATION_SCHEMA+system catalog
SQL Query
WITH target_indexes AS (
    SELECT 
        n.nspname AS schema_name,
        t.relname AS table_name,
        idx.relname AS index_name,
        c.conname AS constraint_name,
        t.oid AS table_oid,
        i.indkey
    FROM pg_index i
    INNER JOIN pg_class t ON i.indrelid = t.oid
    INNER JOIN pg_class idx ON i.indexrelid = idx.oid
    INNER JOIN pg_namespace n ON t.relnamespace = n.oid    
    LEFT JOIN pg_constraint c ON c.conindid = i.indexrelid AND c.contype = 'u'
    WHERE i.indisunique = TRUE 
      AND i.indnullsnotdistinct = TRUE
      AND t.relkind IN ('r', 'p') 
      AND n.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')      
      AND 0 <> ALL(i.indkey) 
),
index_columns AS (
    SELECT 
        ti.schema_name, 
        ti.table_name, 
        ti.index_name, 
        ti.constraint_name,
        a.attname AS column_name,
        a.attnotnull
    FROM target_indexes ti    
    CROSS JOIN unnest(ti.indkey) AS k(col_num)
    INNER JOIN pg_attribute a ON a.attrelid = ti.table_oid AND a.attnum = k.col_num
    WHERE a.attisdropped = FALSE
),
aggregated_indexes AS (
    SELECT 
        schema_name, 
        table_name, 
        index_name, 
        constraint_name,
        string_agg(column_name, ', ' ORDER BY column_name) AS index_columns,        
        bool_and(attnotnull) AS all_columns_not_null
    FROM index_columns
    GROUP BY schema_name, table_name, index_name, constraint_name
)
SELECT 
    schema_name, 
    table_name, 
    COALESCE(constraint_name, index_name) AS object_name,
    CASE WHEN constraint_name IS NOT NULL THEN 'CONSTRAINT' ELSE 'INDEX' END AS object_type,
    index_columns
FROM aggregated_indexes
WHERE all_columns_not_null = TRUE
ORDER BY schema_name, table_name, object_type, object_name;

Fixing Suggestion

To resolve this issue and clean up the database schema, drop the redundant constraint or index and recreate it without the NULLS NOT DISTINCT clause. A standard UNIQUE constraint is perfectly sufficient and logically clearer for mandatory columns.

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:

Missing data

Queries of this category provide information about missing data (NULLs) in a database.

Uniqueness

Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.

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