Introduction to PostgreSQL FETCH clause

To skip a certain number of rows and retrieve a specific number of rows, you often use theLIMIT clause in theSELECT statement.

TheLIMIT clause is widely used by many Relational Database Management Systems such as MySQL, H2, and HSQLDB. However, theLIMIT clause is not a SQL standard.

To conform with the SQL standard, PostgreSQL supports theFETCH clause to skip a certain number of rows and then fetch a specific number of rows.

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

The following illustrates the syntax of the PostgreSQLFETCH clause:

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

In this syntax:

First, specify the number of rows to skip (row_to_skip) after theOFFSET keyword. The start is an integer that is zero or positive. It defaults to 0, meaning the query will skip no rows.

If therow_to_skip is higher than the number of rows in the table, the query will return no rows.

Second, provide the number of rows to retrieve (row_count) in theFETCH clause. Therow_count must be an integer 1 or greater. Therow_count defaults to 1.

TheROW is the synonym forROWS,FIRST is the synonym forNEXT so you can use them interchangeably.

Because the table stores the rows in an unspecified order, you should always use theFETCH clause with theORDER BY clause to make the order of rows consistent.

Note that theOFFSET clause must come before theFETCH clause in SQL:2008. However,OFFSET andFETCH clauses can appear in any order in PostgreSQL.

FETCH vs. LIMIT

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

PostgreSQL FETCH examples

Let’s use thefilm table in thesample database for the demonstration.

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

SELECT    film_id,    titleFROM    filmORDER BY    titleFETCH FIRST ROW ONLY;

Output:

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

It is equivalent to the following query:

SELECT    film_id,    titleFROM    filmORDER BY    titleFETCH FIRST 1 ROW ONLY;

The following query uses theFETCH clause to select the first five films sorted by titles:

SELECT    film_id,    titleFROM    filmORDER BY    titleFETCH 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,    titleFROM    filmORDER BY    titleOFFSET 5 ROWSFETCH 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)

Summary

• Use the PostgreSQLFETCH clause to skip a certain number of rows and retrieve a specific number of rows from a query.

Last updated onJanuary 17, 2024

On this page

• Introduction to PostgreSQL FETCH clause
• PostgreSQL FETCH examples
• Summary