did you try changing "start" and "end" as aliases ?
while "start" on postgresql isn't reserved ( but is a reserved keyword for
t-sql:2003 and t-sql:1999), "end" is reserved even for postgresql.
On Monday, January 7, 2013 1:21:27 PM UTC+1, Paolo valleri wrote:
>
> Hi all, I've problems on defining self join queries on postgres, the
> query, works on sqlite, is defined as follows:
>
> start = db.record.with_alias('start')
> end = db.record.with_alias('end')
> query = ((start.station_id == 11) &
> (end.station_id == 12))
> rows = db( query ).select(start.gathered_on,start.mac, end.gathered_on,
> end.mac,
> orderby=start.gathered_on.epoch(),
> left= start.on( (start.mac == end.mac) ))
> The table is:
> db.define_table('record',
> Field('station_id', 'reference station'),
> Field('log_id', 'reference log'),
> Field('mac'),
> Field('gathered_on', 'datetime'),
> )
>
> The generated query is:
> SELECT start.gathered_on, start.mac, end.gathered_on, end.mac
> FROM record AS end
> LEFT JOIN record AS start ON (start.mac = end.mac) WHERE ((start.station_id
> = 11) AND (end.station_id = 12))
> ORDER BY EXTRACT(epoch FROM start.gathered_on);
>
> and it fails raising the following error:
>
> Traceback (most recent call last):
> File "/home/paolo/Dropbox/git/web2py/gluon/restricted.py", line 212, in
> restricted
> exec ccode in environment
> File
> "/home/paolo/Dropbox/git/web2py/applications/vtraffic/controllers/default.py"
> <http://127.0.0.1:8000/admin/default/edit/vtraffic/controllers/default.py>,
> line 655, in <module>
> File "/home/paolo/Dropbox/git/web2py/gluon/globals.py", line 193, in
> <lambda>
> self._caller = lambda f: f()
> File
> "/home/paolo/Dropbox/git/web2py/applications/vtraffic/controllers/default.py"
> <http://127.0.0.1:8000/admin/default/edit/vtraffic/controllers/default.py>,
> line 37, in index
> left= start.on( (start.mac == end.mac) ))
> File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 8966, in select
> return adapter.select(self.query,fields,attributes)
> File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1636, in select
> return self._select_aux(sql,fields,attributes)
> File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1601, in
> _select_aux
> self.execute(sql)
> File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1714, in execute
> return self.log_execute(*a, **b)
> File "/home/paolo/Dropbox/git/web2py/gluon/dal.py", line 1708, in
> log_execute
> ret = self.cursor.execute(*a, **b)
> ProgrammingError: syntax error at or near "end"
> LINE 1: SELECT start.gathered_on, start.mac, end.gathered_on, end.m...
>
>
> What should I have to do?
> Regards,
> Paolo
>
--
