Postgres 12

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?.

CREATE TABLE tempA
(
    id serial PRIMARY KEY,
    name text UNIQUE NOT NULL
);


CREATE TABLE tempb
(
    id serial PRIMARY KEY,
    tempa_id integer NOT NULL,
    description text NOT NULL,
    CONSTRAINT foo_bar_fk FOREIGN KEY (tempa_id)
        REFERENCES tempa (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
);

INSERT INTO tempA (name) VALUES('tom');
INSERT INTO tempA (name) VALUES('bob');

INSERT INTO tempB (tempA_id, description) SELECT id, 'test1' FROM tempA WHERE tempA.name = 'tom';
INSERT INTO tempB (tempA_id, description) SELECT id, 'test2' FROM tempA WHERE tempA.name = 'tom';
INSERT INTO tempB (tempA_id, description) SELECT id, 'test3' FROM tempA WHERE tempA.name = 'bob';
INSERT INTO tempB (tempA_id, description) SELECT id, 'test4' FROM tempA WHERE tempA.name = 'bob';
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from tempB;


id
tempa_id
description


1
1
test1

2
1
test2

3
2
test3

4
2
test4


with 
	names (to_keep, to_del) as(values('tom','bob')),
	upd as (
		update tempB
		set tempA_id = a_keep.id
		from names n
		inner join tempA a_keep on a_keep.name = n.to_keep
		inner join tempA a_del  on a_del.name  = n.to_del
		where tempB.tempA_id = a_del.id
		returning a_del.id
	)
delete from tempA 
using upd
where tempA.id = upd.id
1 rows affected

select * from tempB


id
tempa_id
description


1
1
test1

2
1
test2

3
1
test3

4
1
test4


select * from tempA


id
name


1
tom