Postgres order_by select count column missing group_by

query.ex

bad_query = from a in Appointment,
  where: is_nil(a.deleted_at),
  order_by: [desc: a.expected_at],
  select: count(a.id)

good_query = from a in Appointment,
  where: is_nil(a.deleted_at),
  select: count(a.id)

Repo.one bad_query # throws ** (Postgrex.Error) ERROR (grouping_error): column "a0.expected_at" must appear in the GROUP BY clause or be used in an aggregate function

Repo.one good_query # gives good result

rant.markdown

The problem

Postgres 9.1 (and whatever version Heroku uses) may periodically give the following bug when trying to select count:

** (Postgrex.Error) ERROR (grouping_error): column "a0.expected_at" must appear in the GROUP BY clause or be used in an aggregate function

where a0.expected_at is some field in your table where you're possibly ordering by. I encountered this "bug" while using Elixir's ecto to write some queries (example provided below), but if you're encountering it elsewhere, it's because order_by and select count should not be used together in postgres.

TL;DR

Remove order_by statements when counting!

Rant (NSFW and edgy and really shouldn't be read by anyone)

Postgres is a piece of shit compared to mysql and its successor maria. Fuck you, postgres, fucking get wrecked. You're difficult to setup, overly annoying with your "roles" and permissions tied in with the user account, and the horseshit ton of cli bins you dump onto my machine. You were clearly designed by narrow-minded engineers who couldn't program an intuitive UI to save their worthless lives.

Fuck you so much, postgres, you're only popular because a large group of shit-face Berkeley "hurr-durr muh performance" engineer dipshits decided they enjoyed using your shit interface. I hope every postgres engineer dies in horrible ways like toxic shock from having diarrhea funneled down their throats, or beheaded by a terrorist group.