Thread: incorrect syntax for offset

Hi.

Our current docs says that we support the following syntax (i'm
putting just the lines that disturbes me) inside a SELECT statement
"""
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
"""

i read that as allow me to use the ROWS in an OFFSET clause whatever i
use LIMIT or FETCH for limiting results but seems like we try hard to
make a distinguish from old syntax and new (sql standard) syntax

pruebas=# select * from tab1 limit 3 offset 2 rows;
ERROR:  syntax error at or near "rows"
LINE 1: select * from tab1 limit 3 offset 2 rows;
                                            ^
pruebas=# select * from tab1 offset 2 fetch next 3 rows only;
ERROR:  syntax error at or near "fetch"
LINE 1: select * from tab1 offset 2 fetch next 3 rows only;
                                    ^
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> i read that as allow me to use the ROWS in an OFFSET clause whatever i
> use LIMIT or FETCH for limiting results but seems like we try hard to
> make a distinguish from old syntax and new (sql standard) syntax

> pruebas=# select * from tab1 limit 3 offset 2 rows;
> ERROR:  syntax error at or near "rows"
> LINE 1: select * from tab1 limit 3 offset 2 rows;
>                                             ^
> pruebas=# select * from tab1 offset 2 fetch next 3 rows only;
> ERROR:  syntax error at or near "fetch"
> LINE 1: select * from tab1 offset 2 fetch next 3 rows only;

Hmm.  Seems like we could refactor the select_limit production so it
actually allows all the reasonable combinations.  Let me have a look...

            regards, tom lane

Jaime Casanova wrote:
> Hi.
>
> Our current docs says that we support the following syntax (i'm
> putting just the lines that disturbes me) inside a SELECT statement
> """
> [ LIMIT { count | ALL } ]
> [ OFFSET start [ ROW | ROWS ] ]
> [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
> """
>
> i read that as allow me to use the ROWS in an OFFSET clause whatever i
> use LIMIT or FETCH for limiting results but seems like we try hard to
> make a distinguish from old syntax and new (sql standard) syntax
>
> pruebas=# select * from tab1 limit 3 offset 2 rows;
> ERROR:  syntax error at or near "rows"
> LINE 1: select * from tab1 limit 3 offset 2 rows;
>                                             ^
> pruebas=# select * from tab1 offset 2 fetch next 3 rows only;
> ERROR:  syntax error at or near "fetch"
> LINE 1: select * from tab1 offset 2 fetch next 3 rows only;

FYI, this is fixed in 9.0:

    test=> select * from tab1 limit 3 offset 2 rows;
     x
    ---
    (0 rows)

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +