Create a pivot table with PostgreSQL

The best way to build pivot tables in Postgres are CASE expressions.

SELECT neighborhood,
       round(avg((CASE WHEN bedrooms = 0 THEN price END)), 2) AS "0",
       round(avg((CASE WHEN bedrooms = 1 THEN price END)), 2) AS "1",
       round(avg((CASE WHEN bedrooms = 2 THEN price END)), 2) AS "2",
       round(avg((CASE WHEN bedrooms = 3 THEN price END)), 2) AS "3"
FROM listings
GROUP BY neighborhood;

Running this on the question data yields

NEIGHBORHOOD                  0          1          2          3
-------------------- ---------- ---------- ---------- ----------
downtown                 256888     334000       NULL       NULL
riverview                  NULL     505000       NULL       NULL

First compute the average with the aggregate function avg():

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

Then feed the result to the crosstab() function as instructed in great detail in this related answer:

• PostgreSQL Crosstab Query