Howdy! I have a bulk update that I want to do that references a column that has a deferred unique constraint on it - meaning it only checks the uniqueness at the end of the transaction.
The postgres description looks like:
"queue_slot_rank_key" UNIQUE CONSTRAINT, btree ("rank") DEFERRABLE
It's usable by doing something like:
BEGIN;
SET CONSTRAINTS "queue_slot_rank_key" DEFERRED;
UPDATE queue_slot SET rank = 2 WHERE id = 1;
UPDATE queue_slot SET rank = 1 WHERE id = 2;
COMMIT;
(assume the "rank" column matched the "id" column before)
This allows me to modify a custom ordering of the rows while maintaining uniqueness on the "rank" column. BTW it also works to do SET CONSTRAINTS ALL DEFERRED
. I can't get the bulk update GUI to work. Is there a way to add transaction directives other than doing a custom SQL query?
If not, is there a safe way to do bulk updates in the sql page? There would be a variable number of them, so I couldn't just format a set number of single value update commands.
When I tried to do it in the raw SQL mode, I was thinking something like this:
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE queue_slot
SET rank = updated.rank
FROM (
SELECT
UNNEST({{ [ list of ids... ] }}) AS "id",
UNNEST({{ [ list of updated rank... ] }}) AS "rank"
) as updated
WHERE queue_slot.id = updated.id;
COMMIT;
But I get "cannot insert multiple commands into a prepared statement" - so then I thought to try to pass them into a prepared query. So on psql
I defined:
prepare reorder (text[], int[]) as
update queue_slot set rank = updated.rank from (select unnest($1) as "id", unnest($2) as "rank") as updated where queue_slot.id = updated.id;
and in retool changed the SQL to be:
EXECUTE reorder({{ [ list of ids...] }}, {{ [ list of rank... ] }});
But that errors with bind message supplies 2 parameters, but prepared statement "" requires 0
.
So I'm guessing it isn't supported to do this, but I'd like it to be possible. If it is possible, can someone point me in the right direction? Thanks!