Goal This query identifies UNIQUE constraints that explicitly use the NULLS NOT DISTINCT clause. Introduced in PostgreSQL 15, this modifier changes standard SQL behavior by treating NULL values as equal, thereby preventing multiple identical rows that contain NULLs. This query is useful for auditing the schema to review where this specific null-handling logic is applied, ensuring it aligns with business rules, or flagging it for environments that require compatibility with older PostgreSQL versions
Notes Refers to the column pg_index.indnullsnotdistinct and thus works starting from PostgreSQL 15.
Type General Overview of some aspect of the database.
License MIT (opens in new tab)
Data Source system catalog only
SQL Query
WITH keys AS (
    SELECT 
        o.conname, 
        nc.nspname AS key_schema,
        c.relname AS key_table, 
        c.oid AS key_table_oid,
        o.conkey AS key_col,
        CASE WHEN o.contype = 'p' THEN 'PRIMARY KEY' ELSE 'UNIQUE' END AS contype
    FROM pg_constraint o 
    INNER JOIN pg_class c ON c.oid = o.conrelid
    INNER JOIN pg_namespace AS nc ON nc.oid = c.relnamespace
    INNER JOIN pg_authid AS a ON nc.nspowner = a.oid    
    INNER JOIN pg_index i ON o.conindid = i.indexrelid
    WHERE (nc.nspname = 'public' OR rolname <> 'postgres')
      AND o.contype IN ('u')       
      AND i.indnullsnotdistinct = TRUE 
),
keys_unnest AS (
    SELECT conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin, contype
    FROM keys, unnest(keys.key_col) WITH ORDINALITY AS k(key_col_num, ordin)
),
keys_with_names AS (
    SELECT conname, key_schema, key_table, contype, array_agg(a_key.attname ORDER BY ordin) AS key_col
    FROM keys_unnest k 
    INNER JOIN pg_attribute a_key ON k.key_col_num = a_key.attnum 
        AND k.key_table_oid = a_key.attrelid 
        AND a_key.attisdropped = FALSE
    GROUP BY conname, key_schema, key_table, contype
)
SELECT conname, key_schema, key_table, contype, key_col
FROM keys_with_names
ORDER BY key_schema, key_table, contype, key_col;

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.

Validity and completeness

Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).

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.
Validity and completenessQueries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).