'Is there a way to use multiple targets in a SQL `ON CONFLICT` clause?

I'm trying to modify a seed script to not insert duplicate records in a postgres db when run multiple times. In one of the tables, each record has a deleted_at column, which can be either NULL (default) or a timestamp (2021-11-12 19:24:47.164380 +00:00). There can be only one record that has a NULL value, and one record with any given timestamp; only one record may be active, and multiple records may have been deleted at some point in the past.

I have two unique indexes on this table:

CREATE UNIQUE INDEX deleted_idx
    ON values (m_id, pp_id, deleted_at)
        WHERE deleted_at IS NOT NULL;

CREATE UNIQUE INDEX active_idx
    ON values (m_id, pp_id, (deleted_at IS NULL))
        WHERE deleted_at IS NULL;

The problem is, in order to use these indexes to prevent duplicates without causing the seed script to error out, I need to do something like this:

INSERT INTO values(m_id, pp_id, deleted_at, ...)
VALUES ('96313007-01e9-42e8-b946-58cc05ce2c8d', '3d8f3be5-a419-4252-acba-7a3eb40ae095', NULL, ...)
ON CONFLICT (m_id, pp_id, (deleted_at IS NULL))
    DO NOTHING; 
ON CONFLICT (m_id, pp_id, deleted_at)
    DO NOTHING;

Keep in mind, m_id is not the PKEY. Obviously I can't do this though, since having two ON CONFLICT clauses isn't valid syntax.

Is there a way I can have two ON CONFLICT targets? Due to business needs, modifying the seed script to insert specific PKEYs so I could just simply enforce uniqueness on the PKEY would be a big deal.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source