I don't know how it possibly worked in sqlite, but this is an aggregate query combined with a non-aggregate query.
In other words, the count is a property of an aggregation of rows in the database, whereas created_on is a property of individual rows. This confuses Postgres. It doesn't know if you want the aggregate result (count) or the result for individual rows (created_on). It cannot deliver both from the same query. What are you trying to find out in your query? On Tuesday, October 16, 2012 4:59:36 PM UTC-4, Paolo wrote: > > Dear all, > I've just switched from sqlite to postgres, and now I have problems with > few queries. > One query that works correctly on sqlite and fails on postgres is the > following: > s=db.club.created_on.year() | db.club.created_on.month() | > db.club.created_on.day() > count = db.club.id.count() > dates = > db(query).select(db.club.created_on,count,orderby=~db.club.created_on, > limitby=limitby, groupby=s) > > Now on postgres, it raises the following error: > ProgrammingError: column "club.created_on" must appear in the GROUP BY > clause or be used in an aggregate function > LINE 1: SELECT club.created_on, COUNT(club.id) FROM club WHERE (((c... > > I read online that the fields in the select must be on the groupby as > well. The problem is that by grouping even by club.created_on (by adding > groupby=s | club.created_on) the result is totally different. What can I > do to tackle this problem? > > Best, > Paolo > --
