PostgreSQL – FETCH clause

The PostgreSQL FETCH clause has a functionality similar to the PostgreSQL LIMIT clause. It is used to retrieve a portion of rows returned by a query. As the LIMIT clause is not a standard SQL-command, PostgreSQL provides a standard way of fetching a subset of results from a query.

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

Let’s analyze the above syntax:

• ROW and FIRST are synonymous with ROWS and NEXT respectively.
• The start is an integer value that is either zero or positive. By default, it is zero.
• The row_count is either one or higher. By default, it is one.
• As the order of rows stored in the table is unpredictable, one should always use the FETCH clause with the ORDER BY clause to make the result set consistent.

For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link.

Now, let’s look into a few examples.

Example 1:
Here we will query the first 10 rows of the film sorted by “title” from the film table of our sample database.

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

Output:

Example 2:
Here we will query the first 10 rows of the film after the first five films, sorted by “title” from the film table of our sample database.

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

Output: