Postgres 11

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 users (user_id int, user_name text);

insert into users values
(1, 'user1'),(2, 'user2'),(3, 'user3'),(4, 'user4'),(5, 'user5');
5 rows affected

create table products (user_id int, id int, name text, att1 text);

insert into products values
(2, 1, 'prod1', 'att1'),
(2, 2, 'prod2', 'att1'),
(2, 3, 'prod3', 'att1'),
(2, 4, 'prod4', 'att1'),
(3, 5, 'prod5', 'att1'),
(3, 6, 'prod6', 'att1');
6 rows affected

select
    users.*,
    json_agg(products.*) as products
from
    users
left join products on
    users.user_id = products.user_id;
ERROR:  column "users.user_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:     users.*,
            ^


select 
    users.*,
    (select 
        json_agg(products.*)
     from
        products
     where
        user_id = users.user_id)  as products
from
    users
limit 3;


user_id
user_name
products


1
user1
null

2
user2
[{"user_id":2,"id":1,"name":"prod1","att1":"att1"}, 
 {"user_id":2,"id":2,"name":"prod2","att1":"att1"}, 
 {"user_id":2,"id":3,"name":"prod3","att1":"att1"}, 
 {"user_id":2,"id":4,"name":"prod4","att1":"att1"}]

3
user3
[{"user_id":3,"id":5,"name":"prod5","att1":"att1"}, 
 {"user_id":3,"id":6,"name":"prod6","att1":"att1"}]

user_id	user_name	products
1	user1	null
2	user2	[{"user_id":2,"id":1,"name":"prod1","att1":"att1"}, {"user_id":2,"id":2,"name":"prod2","att1":"att1"}, {"user_id":2,"id":3,"name":"prod3","att1":"att1"}, {"user_id":2,"id":4,"name":"prod4","att1":"att1"}]
3	user3	[{"user_id":3,"id":5,"name":"prod5","att1":"att1"}, {"user_id":3,"id":6,"name":"prod6","att1":"att1"}]