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 table_1(
	table_1_id int,
	table_2_id_list int[],
	table_2_geom text[]
);

insert into table_1 values 
	(1, ARRAY[1,3,5], null) ,(2, ARRAY[2,4,6], null);
2 rows affected

create table table_2(table_2_id int, geom text);
insert into table_2 values
	(1, 'geom1'), (2, 'geom2'), (3, 'geom3'),
	(4, 'geom4'), (5, 'geom5'), (6, 'geom6');
6 rows affected

select 
	table_1_id, 
	array_agg(table_2.geom order by ordinality) 
from 
	table_1
	cross join lateral unnest(table_2_id_list) with ordinality a 
	inner join table_2 on a = table_2.table_2_id 
group by table_1_id


table_1_id
array_agg


1
{geom1,geom3,geom5}

2
{geom2,geom4,geom6}


	select 
		t1.table_1_id, 
		array_agg(t2.geom order by ordinality) table_2_geom
	from 
		table_1 t1
		cross join lateral unnest(t1.table_2_id_list) with ordinality i(table_2_id) 
		inner join table_2 t2 on t2.table_2_id = i.table_2_id
	group by t1.table_1_id


table_1_id
table_2_geom


1
{geom1,geom3,geom5}

2
{geom2,geom4,geom6}


with cte as (
	select 
		t1.table_1_id, 
		array_agg(t2.geom order by ordinality) table_2_geom
	from 
		table_1 t1
		cross join lateral unnest(t1.table_2_id_list) with ordinality i(table_2_id) 
		inner join table_2 t2 on t2.table_2_id = i.table_2_id
	group by t1.table_1_id
)
update table_1 t1
set table_2_geom = c.table_2_geom
from cte c
where c.table_1_id = t1.table_1_id
2 rows affected

select * from table_1


table_1_id
table_2_id_list
table_2_geom


1
{1,3,5}
{geom1,geom3,geom5}

2
{2,4,6}
{geom2,geom4,geom6}