Postgres 9.5

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?.

drop table if exists my_table;
create table my_table(id int primary key, name text, tags int[]);
insert into my_table values
(1, 'xyz', array[4, 5]);

drop table if exists tags;
create table tags(id int primary key, name text, description text);
insert into tags values
(4, 'tag_name_4', 'tag_description_4'),
(5, 'tag_name_5', 'tag_description_5');

INSERT 0 2


select t.id, t.name, array_agg(array[g.name, g.description])
from my_table as t
cross join unnest(tags) as tag
join tags g on g.id = tag
group by t.id;


id
name
array_agg


1
xyz
{{tag_name_4,tag_description_4},{tag_name_5,tag_description_5}}



SELECT 1


select t.id, t.name, array_agg(concat_ws(', ', g.name, g.description))
from my_table as t
cross join unnest(tags) as tag
join tags g on g.id = tag
group by t.id;


id
name
array_agg


1
xyz
{"tag_name_4, tag_description_4","tag_name_5, tag_description_5"}



SELECT 1


select t.id, t.name, string_agg(concat_ws(', ', g.name, g.description), '; ')
from my_table as t
cross join unnest(tags) as tag
join tags g on g.id = tag
group by t.id;


id
name
string_agg


1
xyz
tag_name_4, tag_description_4; tag_name_5, tag_description_5



SELECT 1


select t.id, t.name, jsonb_object_agg(g.name, g.description)
from my_table as t
cross join unnest(tags) as tag
join tags g on g.id = tag
group by t.id;


id
name
jsonb_object_agg


1
xyz
{"tag_name_4": "tag_description_4", "tag_name_5": "tag_description_5"}



SELECT 1