[kanobi]

Whenever I needed to do a pivot in postgres, I used this approach that is described in this stackoverflow anwser:

https://stackoverflow.com/questions/20618323/create-a-pivot-...

So for example when you have a table like described (column_name, meta_key, value), you would create a query like this:

  SELECT
    column_name,
    MAX(CASE WHEN meta_key='total_rows' THEN value ELSE NULL END) AS total_rows,
    MAX(CASE WHEN meta_key='not_null_count' THEN value ELSE NULL END) AS not_null_count,
    -- for all other metrics....
  FROM tall_table
  GROUP BY 1

(edit: formatting)

[jasonpbecker]

Most of the time I'm using `filter ... where` for cases like these... for example

  select
    column_name,
    MAX(value) FILTER (where meta_key='total_rows') as total_row,
    MAX(value) FILTER (where meta_key='not_null_count') as not_null_count,
    ROUND(SUM (amount_in_cents) FILTER (WHERE EXTRACT(MONTH   FROM TIMESTAMP '2006-01-01 03:04:05) = 1) / 100.0, 2) as 'january_sub_total'
  FROM table
  GROUP BY column_name

[kanobi]

Yup, that's even better. I think it didn't work for me because I was using Redshift, which didn't support FILTER at that time.