I have a table with unique index on two fields, lane_id and position.
Now, I want to update row using this query:
UPDATE "teams_ticket" SET "position" = ("teams_ticket"."position" + 1) WHERE ("teams_ticket"."lane_id" = 1 AND "teams_ticket"."position" >= 0)
Which ends up with:
duplicate key value violates unique constraint "teams_ticket_position_bfcce9fa_uniq"
If I have more than one ticket.
How can I solve this issue?
You need deferrable constraints (and actually defer them, temporally). Deferrable means that the constraints are not checked immediately (say: when the index-tuple is being rewritten) but at the end of the transaction (or statement), when all rows have been updated:
-- \i tmp.sql
CREATE TABLE positions
( seq SERIAL PRIMARY KEY
, position INTEGER NOT NULL UNIQUE DEFERRABLE
);
INSERT INTO positions(position)
SELECT generate_series(1,10) gs;
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
update positions
SET position= position +1
WHERE seq <= 6 ;
SELECT * FROM positions ;
UPDATE positions SET position= position +1
WHERE seq > 6
;
SELECT * FROM positions ;
UPDATE positions SET position= position +1
;
SELECT * FROM positions ;
COMMIT;
You can use cursor to update them one by one.
DO $$
DECLARE r record;
BEGIN
FOR r IN SELECT lane_id, position FROM teams_ticket WHERE lane_id=1 AND position >= 0 ORDER BY position DESC
LOOP
UPDATE teams_ticket SET position=position+1 WHERE position=r.position AND lane_id=r.lane_id;
END LOOP;
END$$;
You can try
UPDATE teams_ticket t
SET position = q.position + 1
FROM (
SELECT lane_id, position
FROM teams_ticket
WHERE lane_id = 1
AND position >= 0
ORDER BY lane_id, position DESC
) q
WHERE t.lane_id = q.lane_id
AND t.position = q.position