PostgreSQL - column "COLUMN" must appear in the GROUP BY clause or be used in an aggregate function LINE 1:

[Dmitrii Ermolaev]

for grp in db((db.pay_ins_stack.ref_==db.pay_ins.id)
                & (db.pay_ins.ref_ == db.deal_acc_addrs.id )
                & (db.deal_acc_addrs.xcurr_id == xcurr.id)
                        ).select(groupby=(db.deal_acc_addrs.id)):

ERROR:

column "pay_ins_stack.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "pay_ins_stack"."id", "pay_ins_stack"."ref_", "pay_in...  

With MySQL all work good !

help please!

[Kevin Keller]

I think @Leonel Câmara would know best what is going on here.

My uneducated guess is that you are using Postgresql12 and that there were some major changes from postgres11 to postgres12.

Either Pydal has not caught up yet, or since pydal is not strictly an ORM, you just have to actually adapt your pydal statements a bit. 

Try this: 

for grp in db((db.pay_ins_stack.ref_==db.pay_ins.id)
                & (db.pay_ins.ref_ == db.deal_acc_addrs.id )
                & (db.deal_acc_addrs.xcurr_id == xcurr.id)

                        ).select(groupby=(db.deal_acc_addrs.id), pay_ins_stack.id):

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/c392a3bc-f748-468a-b74c-5c15fef6d20fn%40googlegroups.com.

[Dmitrii Ermolaev]

    for grp in db((db.pay_ins_stack.ref_==db.pay_ins.id)

                & (db.pay_ins.ref_ == db.deal_acc_addrs.id )

                & (db.deal_acc_addrs.xcurr_id == xcurr.id)

                        ).select(groupby=(db.deal_acc_addrs.id, db.pay_ins_stack.id)):

same error:

column "pay_ins.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ..._ins_stack"."tries", "pay_ins_stack"."to_refuse", "pay_ins".... ^  

суббота, 14 ноября 2020 г. в 12:32:08 UTC+3, kell...@gmail.com:

[valq...@gmail.com]

Pg is not a toy like mysql 

It always required an explicit notation for groupby

воскресенье, 15 ноября 2020 г. в 11:18:55 UTC+3, ermolaev...@gmail.com:

[villas]

Usually, you would only require GROUP BY if you were trying to calculate an aggregate function such as the SUM, COUNT, AVG etc on a column. 

In this case, you are not specifying any such function, so it is unclear what you are trying to do.

Perhaps you are simply trying to express an ORDER BY?  In any case, I suspect it would be best if you read and learn a little more about SQL. 

BTW I have noticed, in particular,  that MySQL and sqllite are extremely forgiving when users specify non-standard SQL commands and you cannot rely on or expect such permissive behaviour across all DB engines.

Best wishes.