These are not valid DAL expressions: db((db.mytable.field1>'C') & 1).select() db((db.mytable.field1>'C') & (1==1)).select() db((db.mytable.field1>'C') & 'True').select() db((db.mytable.field1>'C') & 'TRUE').select()
the argument of db(....) must be a query or a logical expression comprised of queries. 1, True, 'True', 'TRUE' are not queries. The fact that occasionally you do not get an error is purely accidental. You should not use these expressions. Nowhere in the manual we say this is allowed. Future versions of web2py may check (and should) and will block them. In the other case the problem is that the query should be: db(db.mytable.id>0).select(db.mytable.field2, orderby=db.mytable.field2, groupby=db.mytable.field2) and it should work in both cases. If you do not specify that you are selecting only field2, they you are selecting all fields and the query becomes ambiguous (which id should be used for the grouped records?). Sqlite does not care and returns a random one. postgresql checks and complains. Apparently this was solved in SQL3 but most engines do not follow SQL3. http://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function The standard SQL distinct syntax (in DAL: db(...).select(...,distinct=True)) does not solve this problem. So the postgresql folks introduced DISTINCT ON (in DAL: db(...).select(..., distinct=field)) but this is a postgresql specific API. http://www.postgresqlforbeginners.com/2010/11/sql-distinct-distinct-on-and-all.html Massimo On Saturday, 3 January 2015 04:42:14 UTC-6, mweissen wrote: > > In the last hour I have written a small application which tests (1) the > boolean expressions and (2) distinct/groupby. You can find this app as > attach file. > > (1) sqlite allows True or 1 or '1' as boolean expression, postgres works > only with "TRUE" or 'TRUE' > > (2) sqlite does not understand distinct and postgres need some complex > orderby. > > > Testtables Datebase Command Result sqlitedb((db.mytable.field1>'C') & > (1==1)).select()mytable.idmytable.field1mytable.field24Dy5Ey > sqlitedb((db.mytable.field1>'C') > & True).select()mytable.idmytable.field1mytable.field24Dy5Ey > sqlitedb((db.mytable.field1>'C') > & 1).select()mytable.idmytable.field1mytable.field24Dy5Ey > sqlitedb((db.mytable.field1>'C') > & '1').select()mytable.idmytable.field1mytable.field24Dy5Ey > sqlitedb((db.mytable.field1>'C') > & 'TRUE').select()no such column: TRUE sqlitedb((db.mytable.field1>'C') & > "TRUE").select()no such column: TRUE postgresdb((db.mytable.field1>'C') & > (1==1)).select()argument of AND must be type boolean, not type integer > LINE 1: ...le.field2 FROM mytable WHERE ((mytable.field1 > 'C') AND 1); ^ > postgresdb((db.mytable.field1>'C') & True).select()argument of AND must > be type boolean, not type integer LINE 1: ...le.field2 FROM mytable WHERE > ((mytable.field1 > 'C') AND 1); ^ postgresdb((db.mytable.field1>'C') & > 1).select()argument of AND must be type boolean, not type integer LINE 1: > ...le.field2 FROM mytable WHERE ((mytable.field1 > 'C') AND 1); ^ > postgresdb((db.mytable.field1>'C') > & '1').select()argument of AND must be type boolean, not type integer > LINE 1: ...le.field2 FROM mytable WHERE ((mytable.field1 > 'C') AND 1); ^ > postgresdb((db.mytable.field1>'C') & 'TRUE').select()mytable.id > mytable.field1mytable.field24Dy5Ey postgresdb((db.mytable.field1>'C') & > "TRUE").select()mytable.idmytable.field1mytable.field24Dy5Ey sqlitedb( > db.mytable.id>0).select(orderby=db.mytable.field2, > groupby=db.mytable.field2)mytable.idmytable.field1mytable.field22Bx5Ey > sqlitedb(db.mytable.id>0).select(orderby=db.mytable.field2, > distinct=db.mytable.field2)near "ON": syntax error postgresdb( > db.mytable.id>0).select(orderby=db.mytable.field2, > groupby=db.mytable.field2)column "mytable.id" must appear in the GROUP BY > clause or be used in an aggregate function LINE 1: SELECT mytable.id, > mytable.field1, mytable.field2 FROM myta... ^ > postgresdb(db.mytable.id>0).select(orderby=db.mytable.field2, > distinct=db.mytable.field2)mytable.idmytable.field1mytable.field21Ax3Cy > > > 2015-01-03 10:47 GMT+01:00 Marin Pranjić <marin.pran...@gmail.com>: > >> The latter one works only with postgres: >> https://groups.google.com/forum/#!topic/web2py/1_DHUrrg6O8 >> >> What's the error message? >> >> On Sat, Jan 3, 2015 at 5:30 AM, Massimo Di Pierro < >> massimo.dipie...@gmail.com> wrote: >> >>> >>> >>> On Friday, 2 January 2015 16:03:16 UTC-6, mweissen wrote: >>>> >>>> I have tried to convert an application from SQLite to Postgresql and I >>>> have found two differences: >>>> >>>> (1): >>>> q = some query like db.table.field<3 >>>> b = some boolean like a<3 >>>> db(q & b) >>>> >>>> SQLite needs to convert b to '0' or '1', Postgresql to 'FALSE' or 'TRUE' >>>> >>> >>> yes but web2py does it for you. you should not need to change your code. >>> >>> >>>> >>>> (2): orderby generates some difficult error messages. I have found the >>>> following solution >>>> SQLite >>>> db(db.table.field==x).select(orderby=db.table.field2, >>>> groupby=db.table.field2) >>>> >>>> Postgresql: >>>> db(db.table.field==x).select(orderby=db.table.field2, >>>> distinct=db.table.field2) >>>> >>> >>> Are you sure? I think they both work on both databases. The problems >>> arise when you groupby some fields and records are not sorted by those >>> fields. Some engines allow it and some don't. >>> >>> >>>> Does somebody has similar experiences? >>>> >>>> Regards, Martin >>>> >>>> >>>> 2015-01-02 16:29 GMT+01:00 Michele Comitini <michele.comit...@gmail.com >>>> >: >>>> >>>>> SQLite is by design an excellent embedded database, better suited for >>>>> single user implementation and targeted at small datasets. >>>>> For larger datasets there is no reason not to use PostgreSQL, it's >>>>> free, well supported by web2py, >>>>> handles very large datasets and it's easy to setup. >>>>> >>>>> Migration from SQLite to any other db supported by web2py is very easy >>>>> and explained on the web2py book >>>>> >>>>> 2015-01-02 6:49 GMT+01:00 Eric Taw <tawe...@gmail.com>: >>>>> >>>>>> From reading a lot of web2py tutorials and guides, they always say >>>>>> SQLite is good for small implementations of databases, whereas other SQL >>>>>> flavors are better suited for larger uses. Is there any reason why? Can >>>>>> I >>>>>> still use SQLite if I have a lot of information to store? >>>>>> >>>>>> -- >>>>>> 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+unsubscr...@googlegroups.com. >>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>> >>>>> >>>>> -- >>>>> 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+unsubscr...@googlegroups.com. >>>>> For more options, visit https://groups.google.com/d/optout. >>>>> >>>> >>>> >>>> -- >>> 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+unsubscr...@googlegroups.com. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> > > > -- > Mit freundlichen Grüßen / With kind regards > Martin Weissenböck > Gregor-Mendel-Str. 37, 1190 Wien > Austria / European Union > > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
