John Pray's Profile

I fixed this by changing created_at in the select clause to date(created_at). In my case, I also needed to add unscoped. before the where clause in order to eliminate my model's default ordering by name, which PG also complained about for the same reason, and I added a order('date(created_at)') for good measure.

Here's the edited code that works in PostgreSQL:

users = unscoped.where(created_at: start.beginning_of_day..Time.zone.now)
users = users.group('date(created_at)')
users = users.order('date(created_at)')
users = users.select('date(created_at) as created_at, count(*) as count')
users.each_with_object({}) do |user, counts|
        counts[user.created_at.to_date] = user.count
end

I adapted Ryan's example to the code below, which works just fine on SQLite. However, on PostgreSQL (Heroku), I get the error

PGError: ERROR: column "users.created_at" must appear in the GROUP BY clause or be used in an aggregate function

Any ideas on making this work in PostgreSQL?

Here's the relevant code:

users = where(created_at: start.beginning_of_day..Time.zone.now)
users = users.group('date(created_at)')
users = users.select('created_at, count(*) as count')
users.each_with_object({}) do |user, counts|
        counts[user.created_at.to_date] = user.count
end