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} |