Postgres 15

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 _building (
  id int,
  cityId int,	
  location jsonb
);

insert into _building values
(34891,	111,	'{"regionId": "55", "regionName": null, "full address": "..."}'),
(9372,	222,	'{"regionId": null, "regionName": null, "full address": "..."}');

create table _city (
  id int,
  name varchar(10),	
  location jsonb
);

insert into _city values
(111,	'A',	'{"regionId": "55", "regionName": "Magic region 11"}'),
(222,	'B',	'{"regionId": "238", "regionName": "Magic region 23"}');

CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 2


update _building b
set location = jsonb_set(
        b.location,
        '{regionName}',
        c.location -> 'regionName',
        false
    )
from _city c
where c.id = cityId

UPDATE 2


select *
from _building


id
cityid
location


34891
111
{"regionId": "55", "regionName": "Magic region 11", "full address": "..."}

9372
222
{"regionId": null, "regionName": "Magic region 23", "full address": "..."}



SELECT 2

id	cityid	location
34891	111	{"regionId": "55", "regionName": "Magic region 11", "full address": "..."}
9372	222	{"regionId": null, "regionName": "Magic region 23", "full address": "..."}