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?.
begin;
create temp table items(
item_id bigint primary key, name text);
create temp table tags_record_maps(
tag_id bigint,
tag_name text,
tag_color text,
target_record_id bigint references items(item_id));
insert into items values(1,'item1'), (2,'item_2'),(3,'item3');
insert into tags_record_maps (tag_id, tag_name, tag_color, target_record_id)
values
(1, 'real','yellow',1),
(2, 'fake','purple',1),
(3, 'real_1','pink',2),
(4, 'real_2','blue',2),
(5, 'real','red',3);
commit;
3 rows affected
5 rows affected
select i.item_id, i.name, tag_id, tag_name, tag_color
from items i join tags_record_maps trm on i.item_id = trm.target_record_id;
| item_id | name | tag_id | tag_name | tag_color |
|---|---|---|---|---|
| 1 | item1 | 1 | real | yellow |
| 1 | item1 | 2 | fake | purple |
| 2 | item_2 | 3 | real_1 | pink |
| 2 | item_2 | 4 | real_2 | blue |
| 3 | item3 | 5 | real | red |
create type tags as(tag_id bigint, tag_name text,tag_color text);
select item_id,name, array_agg(row(trm.tag_id, tag_name, tag_color)::tags) as tags
from items i join tags_record_maps trm on i.item_id = trm.target_record_id
group by 1,2;
| item_id | name | tags |
|---|---|---|
| 1 | item1 | {"(1,real,yellow)","(2,fake,purple)"} |
| 2 | item_2 | {"(3,real_1,pink)","(4,real_2,blue)"} |
| 3 | item3 | {"(5,real,red)"} |
with a as(
select item_id,name, array_agg(row(trm.tag_id, tag_name, tag_color)::tags) as tags
from items i join tags_record_maps trm on i.item_id = trm.target_record_id
group by 1,2)
select a.item_id, a.tags[2].tag_id from a;
| item_id | tag_id |
|---|---|
| 1 | 2 |
| 2 | 4 |
| 3 | null |