PostgreSQL Tutorial: FETCH NEXT n ROWS ONLY OFFSET m ROWS

Summary: in this tutorial, you will learn how to use the PostgreSQL FETCH clause to retrieve a portion of rows returned by a query.

Table of Contents

Introduction to PostgreSQL FETCH clause

To constrain the number of rows returned by a query, you often use the LIMIT clause. The LIMIT clause is widely used by many relational database management systems such as MySQL, H2, and HSQLDB. However, the LIMIT clause is not a SQL-standard.

To conform with the SQL standard, PostgreSQL supports the FETCH clause to retrieve a number of rows returned by a query.

Note that the FETCH clause was introduced as a part of the SQL standard in SQL:2008.

The following illustrates the syntax of the PostgreSQL FETCH clause:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

In this syntax:

• ROW is the synonym for ROWS, FIRST is the synonym for NEXT . SO you can use them interchangeably
• The start is an integer that must be zero or positive. By default, it is zero if the OFFSET clause is not specified. In case the start is greater than the number of rows in the result set, no rows are returned;
• The row_count is 1 or greater. By default, the default value of row_count is 1 if you do not specify it explicitly.

Because the order of rows stored in the table is unspecified, you should always use the FETCH clause with the ORDER BY clause to make the order of rows in the returned result set consistent.

Note that the OFFSET clause must come before the FETCH clause in SQL:2008. However, OFFSET and FETCH clauses can appear in any order in PostgreSQL.

FETCH vs. LIMIT

The FETCH clause is functionally equivalent to the LIMIT clause. If you plan to make your application compatible with other database systems, you should use the FETCH clause because it follows the standard SQL.

PostgreSQL FETCH examples

Let’s use the film table in the sample database for the demonstration.

The following query use the FETCH clause to select the first film sorted by titles in ascending order:

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

Output:

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

It is equivalent to the following query:

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

The following query use the FETCH clause to select the first five films sorted by titles:

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

Output:

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

The following statement returns the next five films after the first five films sorted by titles:

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

Output:

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

In this tutorial, you have learned how to use the PostgreSQL FETCH clause to retrieve a portion of rows returned by a query.