3

I know LIMIT and OFFSET are pretty common but unstandardized methods to page through a result set. I know there is a standardized method, but I can never find this when I'm going to look it up,

I know it's not either of them. What is the SQL Standard and PostgreSQL syntax for LIMIT and OFFSET?

2

SQL 2011 Spec

The syntax in the spec is defined as,

<query expression> ::=
  [ <with clause> ] <query expression body>
  [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

You can see here that <result offset clause> and the <fetch first clause> are both optional and stand by themselves, moving on

<result offset clause> ::=
  OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
  FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }

<fetch first quantity> ::=
  <fetch first row count>
  | <fetch first percentage>

<offset row count> ::=
  <simple value specification>

<fetch first row count> ::=
  <simple value specification>

<fetch first percentage> ::=
  <simple value specification> PERCENT

<simple value specification> ::=
  <literal>
  | <host parameter name>
  | <SQL parameter reference>
  | <embedded variable name>

RDBMS-Specific

PostgreSQL

The SQL:2011 Spec supports a bit more than PostgreSQL, namely a WITH TIES option (F867) and the ability to specify PERCENT option (F866). The query is defined as,

PostgreSQL only documents this, AFAIK, in SELECT,

SELECT
...
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

For more information aside from PostgreSQL, and the spec see the tag-info for offset-fetch

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.