LIMIT & OFFSET

SELECT select_list
    FROM table_expression
    [ ORDER BY ... ]
    [ LIMIT { number | ALL } ] [ OFFSET number ]

LIMIT specifies that no more than number rows should be returned (can be less). LIMIT ALL and LIMIT NULL are equivalent to omitting the LIMIT clause.

Important

When using LIMIT, it is essential to use an ORDER BY clause, or else you’ll get unpredictable subsets of rows.

mydb=> SELECT * FROM drinks ORDER BY unit_price LIMIT 3;
   name   | unit_price | serving_temp 
----------+------------+--------------
 Lemonade |      $5.50 | cool
 Milk     |      $7.50 | cool
 Tea      |      $9.50 | warm
(3 rows)

mydb=> SELECT * FROM drinks ORDER BY unit_price LIMIT 10;  -- drinks has only 5 rows
   name   | unit_price | serving_temp 
----------+------------+--------------
 Lemonade |      $5.50 | cool
 Milk     |      $7.50 | cool
 Tea      |      $9.50 | warm
 Tea      |      $9.99 | hot
 Coffee   |            | hot
(5 rows)

OFFSET specifies how many rows to skip before returning rows. OFFSET 0 and OFFSET NULL are equivalent to omitting the OFFSET clause.

mydb=> SELECT * FROM drinks ORDER BY unit_price LIMIT 2 OFFSET 2;
 name | unit_price | serving_temp 
------+------------+--------------
 Tea  |      $9.50 | warm
 Tea  |      $9.99 | hot
(2 rows)

Note

The query optimizer takes LIMIT into account when generating query plans.

A large OFFSET may be inefficient since the rows skipped still have to be computed inside the server.