FETCH

The FETCH clause is a SQL-standard way to limit the number of rows returned by a query. While LIMIT is widely used in PostgreSQL, FETCH conforms to the SQL:2008 standard and is preferred when writing portable SQL that must work across different database systems.

FETCH clause syntax

The basic syntax of the PostgreSQL FETCH clause is:

SELECT column1, column2
FROM table_name
ORDER BY column1
OFFSET rows_to_skip ROWS
FETCH FIRST row_count ROWS ONLY;

Key points about the syntax:

• OFFSET specifies how many rows to skip before fetching. It defaults to 0 (no rows skipped). If OFFSET exceeds the total row count, no rows are returned.
• FETCH FIRST and FETCH NEXT are synonyms. ROW and ROWS are also synonyms.
• row_count must be 1 or greater and defaults to 1 when omitted.
• Always pair FETCH with ORDER BY — without ordering, the rows returned are non-deterministic.

PostgreSQL FETCH examples

The following examples use the film table from the PostgreSQL sample database.

Fetch the first film alphabetically:

SELECT film_id, title
FROM film
ORDER BY title
FETCH FIRST ROW ONLY;

film_id |      title
---------+------------------
      1 | Academy Dinosaur
(1 row)

Fetch the first five films:

SELECT film_id, title
FROM film
ORDER BY title
FETCH FIRST 5 ROWS ONLY;

film_id |      title
---------+------------------
      1 | Academy Dinosaur
      2 | Ace Goldfinger
      3 | Adaptation Holes
      4 | Affair Prejudice
      5 | African Egg
(5 rows)

Fetch the next five films (page 2):

SELECT film_id, title
FROM film
ORDER BY title
OFFSET 5 ROWS
FETCH FIRST 5 ROWS ONLY;

film_id |      title
---------+------------------
      6 | Agent Truman
      7 | Airplane Sierra
      8 | Airport Pollock
      9 | Alabama Devil
     10 | Aladdin Calendar
(5 rows)

FETCH vs. LIMIT

FETCH and LIMIT are functionally equivalent in PostgreSQL. Use FETCH when writing SQL intended to be portable across databases (Oracle, DB2, SQL Server) that support the SQL standard. Use LIMIT for brevity in applications that only target PostgreSQL or MySQL.

• Always include ORDER BY with either clause to ensure consistent pagination results.
• For large OFFSET values, consider keyset pagination (filtering on the last seen value) — high offsets require PostgreSQL to scan and discard all skipped rows.
• FETCH FIRST 1 ROW ONLY is a readable way to assert that a subquery returns at most one row.
• In SQL:2008, OFFSET must come before FETCH, but PostgreSQL accepts them in either order.

Reference: PostgreSQL documentation — SELECT.