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 YourTable (
"id" INTEGER,
"name" VARCHAR(6),
"children" VARCHAR(4),
"age" FLOAT
);
INSERT INTO YourTable
("id", "name", "children", "age")
VALUES
('1', 'Rachel', 'john', '20'),
('1', 'Rachel', 'cell', '10'),
('1', 'Rachel', 'jay', '15'),
('2', 'Jereme', 'Les', '17'),
('2', 'Jereme', 'greg', '5.5'),
('2', 'Jereme', 'ven', '27');
CREATE TABLE
INSERT 0 6
SELECT
t.id,
t.name,
MAX(t.children) FILTER (WHERE t.rn = 1) AS child1,
MAX(t.age) FILTER (WHERE t.rn = 1) AS age1,
MAX(t.children) FILTER (WHERE t.rn = 2) AS child2,
MAX(t.age) FILTER (WHERE t.rn = 2) AS age2,
MAX(t.children) FILTER (WHERE t.rn = 3) AS child3,
MAX(t.age) FILTER (WHERE t.rn = 3) AS age3
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY t.id, t.name ORDER BY t.children) AS rn
FROM YourTable t
) t
GROUP BY
t.id,
t.name;
| id | name | child1 | age1 | child2 | age2 | child3 | age3 |
|---|---|---|---|---|---|---|---|
| 1 | Rachel | cell | 10 | jay | 15 | john | 20 |
| 2 | Jereme | greg | 5.5 | Les | 17 | ven | 27 |
SELECT 2