PostgreSQL provides a FETCH clause (not to be confused with FETCH with the use of CURSORS) that is used to fetch/retrieve a part of rows returned by any query. The FETCH clause performs the same functionality as the LIMIT clause. The LIMIT clause is not a standard SQL command, while the FETCH clause is a standard SQL command, so it provides more flexibility/compatibility.

This write-up demonstrates the working of the FETCH clause in PostgreSQL using some examples.

How to Use the FETCH in PostgreSQL?

The below snippet will illustrate the basic syntax of the Postgres FETCH clause:

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

Let’s analyze the above syntax stepwise:

- OFFSET is a clause in Postgres that is used to skip some rows.

- Strat represents an integer value that must be >= 0.

- ROW and ROWS are synonymous with each other similarly, FIRST and NEXT are synonymous with each other.

- row_count represents the number of rows to be fetched, and it must be >=1. By default, its value is 1.

Let’s directly jump into the practical implementation of the FETCH clause.

Example #1: How to Use the FETCH Clause in PostgreSQL?

We already have a table in our database whose details are listed below:

SELECT * FROM article_details;

The output shows that the article_details table has ten rows. Let’s run the following query to fetch the first three rows of the article details table (sorted by article_id in ascending order):

SELECT * FROM article_details
ORDER BY article_id ASC
FETCH FIRST 3 ROW ONLY;

The output shows that the FETCH clause successfully fetched the first three rows of the selected table.

Example #2: How to Skip First Three Rows and Fetch Next Three Rows in PostgreSQL?

Use the Postgres OFFSET clause to skip the first three rows of the table and then use the FETCH CLAUSE to fetch the next three rows:

SELECT * FROM article_details
ORDER BY article_id ASC
OFFSET 3 ROWS
FETCH FIRST 3 ROW ONLY;

The output shows that the FETCH clause succeeded in fetching the next three rows after the first three rows (sorted by article_id).

Example #3: How to Fetch the Last Three Rows of a Table in Postgres?

Sort the article_id in descending order, and utilize the FETCH method to fetch the last three rows:

SELECT * FROM article_details
ORDER BY article_id DESC
FETCH FIRST 3 ROW ONLY;

The output shows that the FETCH clause succeeded in fetching the last three rows of the selected table.

Conclusion

In PostgreSQL, the FETCH clause is used to fetch/retrieve a specific portion/part of rows returned by any query. An optional clause named OFFSET can be used with the FETCH clause to skip some rows of a table. The FETCH clause performs the same functionality as the LIMIT clause. The LIMIT clause is not a standard SQL command, while the FETCH clause is a standard SQL command. Therefore, the FETCH clause provides more flexibility/compatibility. This write-up described the working of the FETCH clause with the help of examples.