PostgreSQL Queries - OFFSET and LIMIT - Guide, Examples and Alternatives

OFFSET and LIMIT options specify how many rows to skip from the beginning, and the maximum number of rows to return by a SQL SELECT statement.

Quick Example:

   -- Return next 10 books starting from 11th (pagination, show results 11-20)
   SELECT * FROM books
   ORDER BY name 
   OFFSET 10 LIMIT 10;

Overview:

Syntax (full...) SELECT … [LIMIT n | ALL] [OFFSET m]
Ordering Applied after ordering if ORDER BY is specified
LIMIT n Return no more than n rows after skipping OFFSET m rows
ALL Return all rows after skipping m rows The same as omitting LIMIT
OFFSET m Skip first m rows after ordering
0 The same as omitting OFFSET
SELECT INTO LIMIT 1 is not required in SELECT INTO variable FROM … as only the first row is taken
and the others are ignored if SELECT INTO STRICT is not specified
Alternative SELECT … OFFSET m FETCH FIRST n ROWS ONLY

Version: PostgreSQL 9.1

PostgreSQL OFFSET and LIMIT Details

OFFSET and LIMIT options can be used to restrict the number of rows returned by the query or provide pagination (output by pages):

   CREATE TABLE num (c1 INT);
 
   -- Insert 10 rows
   INSERT INTO num VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
 
   -- Return first 3 rows
   SELECT * FROM num
   ORDER BY c1
   LIMIT 3;

Both OFFSET and LIMIT applied after sorting. Return 7th and 8th rows:

   SELECT * FROM num
   ORDER BY c1 DESC
   OFFSET 2 LIMIT 2;
   -- Result:
   -- 8
   -- 7

Instead of LIMIT option you can use ANSI/ISO-compliant FETCH FIRST ROWS ONLY. Return 7th and 8th rows:

   SELECT * FROM num
   ORDER BY c1 DESC
   OFFSET 2 FETCH FIRST 2 ROWS ONLY;
   -- Result:
   -- 8
   -- 7

PostgreSQL OFFSET and LIMIT in Other Databases

Limiting the number of rows in other databases:

MySQL:

LIMIT n LIMIT ALL is not supported
OFFSET m Must follow after LIMIT
Short Form LIMIT m, n is equivalent to LIMIT n OFFSET m
FETCH FIRST ROWS ONLY

Resources