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
update table_1 t1
set table_2_geom = (
select array_agg(t2.geom order by ordinality)
from 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
)
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} |