Postgres 16

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

-- provides crosstab() function
CREATE EXTENSION tablefunc;

CREATE EXTENSION


CREATE TABLE listings(id int, neighborhood text, bedrooms int, price int);
INSERT INTO listings VALUES
  (1, 'downtown' , 0, 189000)
, (2, 'downtown' , 3, 450000)
, (3, 'riverview', 1, 300000)
, (4, 'riverview', 0, 250000)
, (5, 'downtown' , 1, 325000)
, (6, 'riverview', 2, 350000)
;

CREATE TABLE



INSERT 0 6


-- Base query to get avg (cast to integer like in your desired result)

SELECT neighborhood, bedrooms, avg(price)::int
FROM   listings
GROUP  BY 1, 2
ORDER  BY 1, 2;


neighborhood
bedrooms
avg


downtown
0
189000

downtown
1
325000

downtown
3
450000

riverview
0
250000

riverview
1
300000

riverview
2
350000



SELECT 6


-- crosstab query
SELECT *
FROM   crosstab(
   'SELECT neighborhood, bedrooms, avg(price)::int
    FROM   listings
    GROUP  BY 1, 2
    ORDER  BY 1, 2;'

  , $$SELECT unnest('{0,1,2,3}'::int[])$$
   ) AS ct ("neighborhood" text, "0" int, "1" int, "2" int, "3" int);


neighborhood
0
1
2
3


downtown
189000
325000
null
450000

riverview
250000
300000
350000
null



SELECT 2


-- With aggregate FILTER clause
SELECT neighborhood
     , avg(price) FILTER (WHERE bedrooms = 0)::int AS "0"
     , avg(price) FILTER (WHERE bedrooms = 1)::int AS "1"
     , avg(price) FILTER (WHERE bedrooms = 2)::int AS "2"
     , avg(price) FILTER (WHERE bedrooms = 3)::int AS "3"
FROM   listings
GROUP  BY 1;


neighborhood
0
1
2
3


downtown
189000
325000
null
450000

riverview
250000
300000
350000
null



SELECT 2

neighborhood	bedrooms	avg
downtown	0	189000
downtown	1	325000
downtown	3	450000
riverview	0	250000
riverview	1	300000
riverview	2	350000