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?.
select version();
| version |
|---|
| PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
create table table1 (category_id integer
,unit text
,is_valid boolean);
insert into table1(category_id, unit, is_valid)
values (1, 'a', true)
, (2, 'z', true)
, (3, 'x', false)
, (2, 'e', false);
select * from table1;
4 rows affected
| category_id | unit | is_valid |
|---|---|---|
| 1 | a | t |
| 2 | z | t |
| 3 | x | f |
| 2 | e | f |
create table table2 (category_id integer
,unit text
) ;
insert into table2(category_id, unit)
values (1, 'a')
, (1, 'b')
, (1, 'c')
, (2, 'd')
, (2, 'e') ;
5 rows affected
update table1 t1
set is_valid = exists (select null
from table2 t2
where (t2.category_id, t2.unit) = (t1.category_id, t1.unit)
);
select * from table1;
4 rows affected
| category_id | unit | is_valid |
|---|---|---|
| 1 | a | t |
| 2 | z | f |
| 3 | x | f |
| 2 | e | t |
-- reset table1
truncate table1;
insert into table1(category_id, unit, is_valid)
values (1, 'a', true)
, (2, 'z', true)
, (3, 'x', false)
, (2, 'e', false);
select * from table1;
4 rows affected
| category_id | unit | is_valid |
|---|---|---|
| 1 | a | t |
| 2 | z | t |
| 3 | x | f |
| 2 | e | f |
with to_valid (category_id, unit, is_valid) as
(select category_id
, unit
, exists (select null
from table2 t2
where (t2.category_id, t2.unit) = (t1.category_id, t1.unit)
)
from table1 t1
)
update table1 tu
set is_valid = to_valid.is_valid
from to_valid
where (tu.category_id, tu.unit) = (to_valid.category_id, to_valid.unit)
and tu.is_valid is distinct from to_valid.is_valid;
select * from table1;
2 rows affected
| category_id | unit | is_valid |
|---|---|---|
| 1 | a | t |
| 3 | x | f |
| 2 | e | t |
| 2 | z | f |