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 tbl (care_team text, city text);
INSERT INTO tbl VALUES
('Team1', 'Franklin')
, ('Team1', 'Hopedale')
, ('Team1', 'Medway')
, ('Team1', 'Milford')
, ('Team2', 'city 1')
, ('Team2', 'city 4')
, ('Team2', 'city 3')
, ('Team2', 'city 2')
;
CREATE TABLE
INSERT 0 8
-- simple 1-parameter form of crosstab()
SELECT *
FROM crosstab(
'SELECT care_team, 1, city
FROM tbl
ORDER BY care_team, city' -- needs to be "ORDER BY 1,2" here
) AS ct ("Team" text, "1" text, "2" text, "3" text, "4" text);
| Team | 1 | 2 | 3 | 4 |
|---|---|---|---|---|
| Team1 | Franklin | Hopedale | Medway | Milford |
| Team2 | city 1 | city 2 | city 3 | city 4 |
SELECT 2
-- Conditional ordered-set agg func
SELECT care_team AS "Team"
, percentile_disc (0) WITHIN GROUP (ORDER BY city) AS "1"
, percentile_disc (.251) WITHIN GROUP (ORDER BY city) AS "2"
, percentile_disc (.501) WITHIN GROUP (ORDER BY city) AS "3"
, percentile_disc (.751) WITHIN GROUP (ORDER BY city) AS "4"
FROM tbl
GROUP BY 1;
| Team | 1 | 2 | 3 | 4 |
|---|---|---|---|---|
| Team1 | Franklin | Hopedale | Medway | Milford |
| Team2 | city 1 | city 2 | city 3 | city 4 |
SELECT 2
-- Your hidden agenda
SELECT care_team AS "Team", string_agg(city, ', ') AS cities
FROM tbl
GROUP BY 1;
| Team | cities |
|---|---|
| Team1 | Franklin, Hopedale, Medway, Milford |
| Team2 | city 1, city 4, city 3, city 2 |
SELECT 2
-- Your hidden agenda with sorted cities
SELECT care_team AS "Team", string_agg(city, ', ' ORDER BY city) AS cities
FROM tbl
GROUP BY 1
ORDER BY 1;
-- the same, faster:
SELECT care_team AS "Team", string_agg(city, ', ') AS cities
FROM (SELECT care_team, city FROM tbl ORDER BY 1, 2) sub
GROUP BY 1;
| Team | cities |
|---|---|
| Team1 | Franklin, Hopedale, Medway, Milford |
| Team2 | city 1, city 2, city 3, city 4 |
SELECT 2
| Team | cities |
|---|---|
| Team1 | Franklin, Hopedale, Medway, Milford |
| Team2 | city 1, city 2, city 3, city 4 |
SELECT 2