Q:

GROUP BY and ordering by date that was extracted as timestamp

I have a rather simple query:
SELECT table.foo, array_agg([ARRAY[EXTRACT(epoch FROM table.date), table.bar]) AS array
  FROM table
 GROUP BY table.foo,
 ORDER BY table.date ASC;

When I run this query I get an error:
ERROR:  column "table.date" must appear in the GROUP BY clause or be used in an aggregate function

I don't quite understand why that is happening because date appears in aggregate function. Is there any way to achieve that grouping?

A:

you cant order by not existing column. If you want to order values in aggregation, use:
SELECT table.foo, array_agg([ARRAY[EXTRACT(epoch FROM table.date), table.bar] ORDER BY table.date ASC) AS array
  FROM table
 GROUP BY table.foo;

