Today I Learned

A Hashrocket project

group by 1, 2, 3 🔢

Postgres allow group by and order by to reference column order in a sql statement. This is particularly useful when an aggregate result needs to be referenced in the group by or order by statement.

-- group by aggregate
select
  (created_at at time zone 'UTC' at time zone 'America/Chicago')::date,
  count(*)
from posts
group by (created_at at time zone 'UTC' at time zone 'America/Chicago')::date
order by (created_at at time zone 'UTC' at time zone 'America/Chicago')::date
;

becomes

-- group by 1, 2, 3
select
  (created_at at time zone 'UTC' at time zone 'America/Chicago')::date,
  count(*)
from posts
group by 1
order by 1
;

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.