Handling Multiple Independent Unique Constraints with ON CONFLICT in PostgreSQL

Problem Context and Core Challenge

In PostgreSQL database design, tables often contain multiple columns with independent uniqueness constraints. For instance, a user table might require both username and email fields to be unique individually. When using the INSERT ... ON CONFLICT ... DO UPDATE syntax introduced in PostgreSQL 9.5 (commonly called UPSERT), developers expect to detect conflicts on multiple unique columns simultaneously, but the syntax does not directly support this operation.

Analysis of Syntax Limitations

According to PostgreSQL documentation, the conflict_target parameter in the ON CONFLICT clause performs conflict detection through unique index inference. This requires an existing unique index as an arbiter index. When specifying ON CONFLICT (col1, col2), the system looks for a composite unique index containing both columns, rather than detecting uniqueness violations on col1 and col2 separately.

Consider the following example table structure:

CREATE TABLE dupes(
    col1 int PRIMARY KEY,
    col2 int,
    col3 text,
    CONSTRAINT col2_unique UNIQUE (col2)
);

Attempting to execute this query:

INSERT INTO dupes VALUES(3,2,'c') 
ON CONFLICT (col1) DO UPDATE SET col3 = 'c', col2 = 2;

When col2=2 already exists, even though col1=3 doesn't conflict, the operation fails due to violation of the col2_unique constraint. Attempting to use ON CONFLICT (col1,col2) results in a syntax error due to the absence of a corresponding composite unique index.

Technical Principles Deep Dive

PostgreSQL's design stems from semantic ambiguity in conflict resolution. When multiple columns have independent uniqueness constraints, the system cannot determine:

1. If col2 conflicts, should col1 be updated?
2. If updating col1 causes new conflicts, how should they be handled?
3. When multiple conflicts occur simultaneously, what should be the priority?

These undefined semantics make direct support for multiple independent unique conflict detection complex. In contrast, composite unique indexes provide clear conflict definitions—only considering it a conflict when the combination of all specified columns is duplicated.

Stored Function Solution

By combining traditional UPSERT logic, we can create a stored function to handle this complex scenario:

CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) 
RETURNS VOID AS $$
BEGIN
    LOOP
        -- First attempt to update existing record
        UPDATE dupes SET col3 = data WHERE col1 = key1 AND col2 = key2;
        IF found THEN
            RETURN;
        END IF;
        
        -- Update failed, attempt to insert new record
        BEGIN
            INSERT INTO dupes VALUES (key1, key2, data) 
            ON CONFLICT (col1) DO UPDATE SET col3 = data;
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            BEGIN
                INSERT INTO dupes VALUES (key1, key2, data) 
                ON CONFLICT (col2) DO UPDATE SET col3 = data;
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- Double conflict, retry the update loop
            END;
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

This function implements the following logic:

1. First attempt to update existing records based on both column conditions
2. If update fails (record doesn't exist), attempt to insert a new record
3. During insertion, first check for col1 conflicts, then col2 conflicts if needed
4. If both columns conflict, restart the loop (other transactions may have updated data)

Alternative Approach: Composite Unique Index

As mentioned in other answers, creating a composite unique index is another solution:

CREATE UNIQUE INDEX idx_table_col1_col2 ON table_name (col1, col2);

Then you can use:

INSERT INTO table_name ... 
ON CONFLICT (col1, col2) DO UPDATE SET ...;

However, this approach changes the semantics of data constraints—it allows col1 or col2 to repeat individually, only prohibiting the combination from being duplicated, which may not meet original business requirements.

Performance and Concurrency Considerations

The stored function solution performs well in concurrent environments because:

1. It uses a retry loop mechanism to handle race conditions
2. Exception handling ensures transaction integrity
3. The update-first strategy reduces lock contention

However, in high-concurrency scenarios, you may need to adjust retry logic or add exponential backoff. The composite unique index approach generally offers better performance, but as noted, may not satisfy business constraint requirements.

Version Compatibility and Upgrade Recommendations

This discussion is based on PostgreSQL 9.5. In newer versions (9.6+), the ON CONFLICT syntax remains compatible with performance optimizations. For scenarios requiring handling of multiple independent unique constraints, we recommend:

1. Evaluate business requirements to determine if multiple independent uniqueness is truly needed
2. Consider using composite unique indexes to simplify design
3. If independent uniqueness must be maintained, adopt the stored function solution presented here
4. In PostgreSQL 12+, explore advanced features like partial indexes and conditional unique constraints

Conclusion

PostgreSQL's ON CONFLICT syntax provides powerful support for data merging but has inherent limitations when dealing with multiple independent unique constraints. By deeply understanding unique index inference mechanisms and conflict detection principles, developers can choose appropriate solutions. The stored function solution offers maximum flexibility, while the composite unique index approach is more concise and efficient when semantic requirements are met. In practical applications, make choices based on specific business needs, performance requirements, and concurrency characteristics.