Unique constraints made redundant by an exclude constraint

Back to Catalog
Goal This query identifies superfluous UNIQUE constraints where the constraint is logically subsumed by a more general EXCLUDE constraint on the same table. It targets cases where the set of columns in a UNIQUE or PRIMARY KEY constraint is a subset of (or equal to) the columns in an EXCLUDE constraint, provided the EXCLUDE constraint uses the equality operator (=) for those same columns. In this scenario, the EXCLUDE constraint already enforces uniqueness as part of its more complex logic, rendering the separate UNIQUE constraint redundant. Eliminating this duplication improves schema clarity and removes an unnecessary constraint check.
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
Fixing Suggestion If a uniqueness constraint is a special case of the exclude constraint, then drop the uniqueness constraint. For instance, in the following case the UNIQUE constraint is redundant: EXCLUDE USING gist (worker_id WITH =, project_id WITH =, tstzrange(begin_time, end_time, '[)'::text) WITH &&)
UNIQUE (begin_time, worker_id, project_id)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH exclude_constraints AS (
SELECT
    all_constraints.table_schema,
    all_constraints.table_name,
    all_constraints.constraint_name,
    all_constraints.constraint_type,
    array_agg(DISTINCT all_constraints.column_name ORDER BY all_constraints.column_name) AS constraint_columns
FROM (
    WITH constraint_info AS (
        SELECT
            c.oid AS constraint_oid,
            ns.nspname,
            cls.relname,
            c.conname,
            c.conindid,
            i.indrelid,
            i.indkey,
            i.indexprs
        FROM
            pg_constraint AS c
        INNER JOIN
            pg_class AS cls ON c.conrelid = cls.oid
        INNER JOIN
            pg_namespace AS ns ON cls.relnamespace = ns.oid
        INNER JOIN
            pg_index AS i ON i.indexrelid = c.conindid
        WHERE
            c.contype = 'x'
            AND ns.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
            AND ns.nspname NOT LIKE 'pg_temp_%'
    )
    SELECT
        ci.nspname AS table_schema,
        ci.relname AS table_name,
        ci.conname AS constraint_name,
        'EXCLUDE' AS constraint_type,
        a.attname AS column_name
    FROM
        constraint_info AS ci
    INNER JOIN
        pg_attribute AS a ON a.attrelid = ci.indrelid AND a.attnum = ANY(ci.indkey)
    WHERE
        a.attnum > 0 AND NOT a.attisdropped

    UNION

    SELECT
        ci.nspname AS table_schema,
        ci.relname AS table_name,
        ci.conname AS constraint_name,
        'EXCLUDE' AS constraint_type,
        expr_cols.column_name
    FROM
        constraint_info AS ci
    INNER JOIN LATERAL (
        SELECT
            matches.column_candidate[1] AS column_name
        FROM
            regexp_matches(pg_get_expr(ci.indexprs, ci.indrelid), '\y([a-zA-Z_]\w*)\y', 'g') AS matches(column_candidate)
        WHERE
            EXISTS (
                SELECT 1
                FROM pg_attribute AS a
                WHERE a.attrelid = ci.indrelid AND a.attname = matches.column_candidate[1]
            )
    ) AS expr_cols ON true
    WHERE
        ci.indexprs IS NOT NULL

) AS all_constraints
GROUP BY
    all_constraints.table_schema,
    all_constraints.table_name,
    all_constraints.constraint_name,
    all_constraints.constraint_type
),
pk_unique_constraints AS (
SELECT
    all_constraints.table_schema,
    all_constraints.table_name,
    all_constraints.constraint_name,
    all_constraints.constraint_type,
    array_agg(DISTINCT all_constraints.column_name ORDER BY all_constraints.column_name) AS constraint_columns
FROM (   
    SELECT
        ns.nspname AS table_schema,
        cls.relname AS table_name,
        c.conname AS constraint_name,
        CASE c.contype
            WHEN 'p' THEN 'PRIMARY KEY'
            WHEN 'u' THEN 'UNIQUE'
        END AS constraint_type,
        a.attname AS column_name
    FROM
        pg_constraint AS c
    INNER JOIN
        pg_class AS cls ON c.conrelid = cls.oid
    INNER JOIN
        pg_namespace AS ns ON cls.relnamespace = ns.oid
    CROSS JOIN LATERAL
        unnest(c.conkey) AS u(attnum)
    INNER JOIN
        pg_attribute AS a ON a.attrelid = c.conrelid AND a.attnum = u.attnum
    WHERE
        c.contype IN ('p', 'u')
        AND ns.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
        AND ns.nspname NOT LIKE 'pg_temp_%'

) AS all_constraints
GROUP BY
    all_constraints.table_schema,
    all_constraints.table_name,
    all_constraints.constraint_name,
    all_constraints.constraint_type
)
SELECT ec.table_schema, ec.table_name, ec.constraint_name AS exclude_constraint_name, ec.constraint_columns AS exclude_constraint_columnns,
puc.constraint_name AS key_constraint_name, puc.constraint_columns AS key_constraint_columnns, puc.constraint_type AS key_constraint_type
FROM exclude_constraints AS ec, pk_unique_constraints AS puc
WHERE ec.table_schema=puc.table_schema
AND ec.table_name=puc.table_name
AND ec.constraint_columns@>puc.constraint_columns
ORDER BY ec.table_schema, ec.table_name, ec.constraint_name;
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.
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).