I use an INSERT ON CONFLICT UPDATE upsert to perform regular syncs from low to high environments. I defer all constraints before the upsert, however I recently had to add a partial unique index which is not deferrable, and it's causing the job to fail.
I was using something like this to work around the anticipated primary key conflicts:
INSERT INTO high_side_table0 (id, col1, col2, col3, boolean1)
SELECT (id, col1, col2, col3, boolean1)
FROM new_or_modified_records_from_low_side_table0
ON CONFLICT (id)
DO UPDATE SET col1 = EXCLUDED.col1, col2 = EXCLUDED.col2, col3 = EXCLUDED.col3, boolean1 = EXCLUDED.boolean1;
The partial unique index I recently added prevents a duplicate combination of three columns in cases where a particular boolean value in that record is FALSE.
CREATE UNIQUE INDEX table0_col2_col3_boolean1_unique ON table0 (col2, col3, boolean1) WHERE NOT boolean1;
If a duplicate+FALSE combination is included in the upsert, it should fail to insert then update the existing (duplicate) row with which it conflicted. I can do this using the same upsert code above but replacing the CONFLICT target with the following: ON CONFLICT (col2, col3, boolean1). Unfortunately, I can't add id to the CONFLICT set ON CONFLICT (id, col2, col3, boolean1) because this seems to treat each column as independent. Nor can I add id to the UNIQUE INDEX because it wouldn't properly catch the duplicates I'm looking for.
To summarize, I need to catch two separate potential constraint violation types but in a single upsert transaction. Something like this:
INSERT INTO high_side_table0 (id, col1, col2, col3, boolean1)
SELECT (id, col1, col2, col3, my_boolean)
FROM new_or_modified_records_from_low_side_table0
ON CONFLICT (id) OR (col2, col3, boolean1)
DO UPDATE SET col1 = EXCLUDED.col1, col2 = EXCLUDED.col2, col3 = EXCLUDED.col3, boolean1 = EXCLUDED.boolean1;
After skimming through a lot of postgres documentation and stack overflow posts, I'm leaning toward creating a custom upsert function (one example shown in the accepted answer here). Would love if there is a built in way to accomplish this, or if anyone has any insight as to why this might be a bad idea in the first place.
