'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 |
|---|
