Upsert with partial unique index - WHERE clause support?
Hi everyone!
I'm running into an issue with Ash's
upsert functionality when dealing with a partial unique index.
My table has a unique index defined as UNIQUE (org_id, name) WHERE org_id IS NULL, which is a partial index. The use case for this choice is that rows in the table can be scoped by tenant, or can be global (multitenancy with global? true)
When Ash generates the INSERT statement with ON CONFLICT, it produces ON CONFLICT ("org_id","name") DO UPDATE..., but postgresql throws an error
The issue is that for partial indexes, postgresql requires the WHERE clause to be included in the ON CONFLICT specification, like: ON CONFLICT ("org_id","name") WHERE org_id IS NULL.
I have seen that Ash has an upsert_condition macro but it adds the WHEREclause on the DO UPDATE SET which is perfectly valid but serves a completely different purpose than the one after ON CONFLICT. It adds a condition for whether the update should actually happen when a conflict is detected.
Does Ash's upsert action support specifying a WHERE clause for the ON CONFLICT` to handle partial unique indexes? If not, is there a recommended workaround for this scenario?
Thanks!2 Replies
Solution
Thanks @barnabasj I am impressed by the framework every day ❤️