add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
begin;
create table testing_stuff (
id serial,
num integer NOT NULL unique not deferrable
);

insert into testing_stuff (num) values (2), (1) returning *;

-- no issues with deferrable even though it swaps values
update testing_stuff set num = id returning *;
BEGIN

CREATE TABLE
id num
1 2
2 1 
INSERT 0 2

ERROR:  duplicate key value violates unique constraint "testing_stuff_num_key"
DETAIL:  Key (num)=(1) already exists.
rollback;
ROLLBACK
begin;
create table testing_stuff (
id serial,
num integer NOT NULL unique deferrable initially immediate
);

insert into testing_stuff (num) values (2), (1) returning *;

-- no issues with deferrable even though it swaps values
update testing_stuff set num = id returning *;

-- fails even though I have not comitted;
update testing_stuff set num = 2 returning *;
BEGIN

CREATE TABLE
id num
1 2
2 1 
INSERT 0 2
id num
1 1
2 2 
UPDATE 2

ERROR:  duplicate key value violates unique constraint "testing_stuff_num_key"
DETAIL:  Key (num)=(2) already exists.
rollback;
ROLLBACK
begin;
create table testing_stuff (
id serial,
num integer NOT NULL unique deferrable initially immediate
);

insert into testing_stuff (num) values (2), (1) returning *;

SET CONSTRAINTS ALL DEFERRED;

-- no issues with deferrable even though it swaps values
update testing_stuff set num = id returning *;

-- fails even though I have not comitted;
update testing_stuff set num = 2 returning *;

-- this would have fixed it
update testing_stuff set num = id returning *;

commit;
BEGIN

CREATE TABLE
id num
1 2
2 1 
INSERT 0 2

SET CONSTRAINTS
id num
1 1
2 2 
UPDATE 2
id num
1 2
2 2 
UPDATE 2
id num
1 1
2 2 
UPDATE 2

COMMIT
table testing_stuff;
id num
1 1
2 2 
SELECT 2