Upsert with partial unique index - WHERE clause support?
Hi everyone!
I'm running into an issue with Ash's
My table has a unique index defined as
When Ash generates the INSERT statement with
Thanks!
I'm running into an issue with Ash's
upsertMy 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!
