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 person (person_id int, person_name text);
INSERT INTO person VALUES (1, 'John'), (2, 'Jill'), (3, 'Mary');
CREATE TABLE pet (pet_id int, owner_id int, pet_name text);
INSERT INTO pet VALUES (1,1,'Fluffy'),(2,1,'Buster'),(3,2,'Doggy');
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 3
select
COALESCE(json_agg(row_to_json(row(p2.pet_id::text, p2.pet_name))) FILTER (WHERE pet_id IS NOT NULL), '[]') as json,
p1.person_name
from person p1
left join pet p2
on p1.person_id = p2.owner_id
group by
p1.person_name;
| json | person_name |
|---|---|
| [] | Mary |
| [{"f1":"3","f2":"Doggy"}] | Jill |
| [{"f1":"1","f2":"Fluffy"}, {"f1":"2","f2":"Buster"}] | John |
SELECT 3