PostgreSQL FETCH - Limit the number of rows returned

In PostgreSQL, you can use the FETCH clause in the SELECT statement to limit the number of rows returned to save system load.

The FETCH clause works the same as the LIMIT clause, but FETCH clause is a part of SQL standard and LIMIT clause is not.

PostgreSQL FETCH syntax

To use the FETCH clause to limit the number of rows returned, use it as follows:

FETCH { FIRST | NEXT } [ rows_count ] { ROW | ROWS } ONLY

Explanation:

• You can use FIRST or NEXT, and they have the same meaning.
• You can use ROW or ROWS, and they have the same meaning.
• The rows_count is the number of rows to limit, that is the maximum number of rows returned. It is optional and defaults to 1. You should specify an integer value greater than 0 for rows_count.

The full usage of a SELECT statement with a FETCH clause is as follows:

SELECT column_list
FROM table_name
[other_clauses]
FETCH rows_count;

The other_clauses are those other clauses that can be used in the SELECT statement, such as WHERE, ORDER BY, and OFFSET etc.

Typically, you need to use FETCH with the ORDER BY clause in a SELECT statement so that you get a result set in the specified order. This is a good practice.

In some applications with paginated queries, you need to use the OFFSET clause in conjunction with the FETCH clause in the SELECT statement, as follows:

SELECT column_list
FROM table_name
ORDER BY ...
OFFSET skipped_rows
FETCH FIRST rows_count ROWS ONLY;

For example:

• The first page can use OFFSET 0 OFFSET FIRST 10 ROWS ONLY that indicates that a maximum of 10 rows are returned.
• The second page can use OFFSET 10 OFFSET FIRST 10 ROWS ONLY that indicates that a maximum of 10 rows are returned after skipping the 10 rows of the first page.
• The third page can be used OFFSET 20 OFFSET FIRST 10 ROWS ONLY that indicates that a maximum of 10 rows are returned after skipping the 20 rows of the first two pages.
• And so on…

PostgreSQL FETCH Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

In the Sakila sample database, the film table stores all the films available for rent in a DVD store.

Example of using PostgreSQL FETCH to limit the number of rows returned

To limit the return of a maximum of 5 rows when querying from the film table, use the following SELECT statement with a FETCH clause:

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

 film_id |      title       | release_year
---------+------------------+--------------
       1 | ACADEMY DINOSAUR |         2006
       2 | ACE GOLDFINGER   |         2006
       3 | ADAPTATION HOLES |         2006
       4 | AFFAIR PREJUDICE |         2006
       5 | AFRICAN EGG      |         2006

Get the first N rows using PostgreSQL FETCH

Typically, you often use the FETCH clause to select the row with the highest or lowest value from a table.

For example, to get the top 10 films for rent, you can sort the films in descending order of rent, then use the FETCH clause to get the top 10 films. You can do this with the following query:

SELECT
  film_id,
  title,
  rental_rate
FROM film
ORDER BY rental_rate DESC, film_id
FETCH FIRST 10 ROWS ONLY;

The query results are as follows:

 film_id |        title         | rental_rate
---------+----------------------+-------------
       2 | ACE GOLDFINGER       |        4.99
       7 | AIRPLANE SIERRA      |        4.99
       8 | AIRPORT POLLOCK      |        4.99
      10 | ALADDIN CALENDAR     |        4.99
      13 | ALI FOREVER          |        4.99
      20 | AMELIE HELLFIGHTERS  |        4.99
      21 | AMERICAN CIRCUS      |        4.99
      28 | ANTHEM LUKE          |        4.99
      31 | APACHE DIVINE        |        4.99
      32 | APOCALYPSE FLAMINGOS |        4.99

Here, to get the top 10 films with the highest rent, we use the ORDER BY rental_rate DESC, film_id clause to sort the films in descending order of rent, and use the FETCH FIRST 10 ROWS ONLY clause to get the top 10 rows.

Example of paging query using PostgreSQL FETCH and OFFSET

There are 1000 rows of information about the movie in the film table. You can verify this with the following SELECT statement with an expression COUNT(*):

SELECT COUNT(*) FROM film;

 count
-------
  1000

Suppose you have a system that needs to display all the films on the front end. It is not a good solution to display all 1000 rows on one page. Because of the following disadvantages of this solution:

• The database performance. Returning a large amount of data in one statement will bring greater memory overhead and IO consumption to the database server.
• The application performance. A large amount of data will cause the application to occupy more memory, and even cause the application to freeze or down.
• The user experience. Users may get dizzy when faced with a large amount of rows.

A better solution is to display all the films in pagination. You can easily implement paginated queries using the SELECT statement with FETCH and OFFSET clauses.

Suppose you need to display 10 films per page, then you can use the following statement to get all the rows of the first page:

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

Here, in order to keep the order of all films, we use ORDER BY film_id to sort the films by film_id, and use FETCH FIRST 10 ROWS ONLY to limit this query to return a maximum of 10 rows.

 film_id |      title       | release_year
---------+------------------+--------------
       1 | ACADEMY DINOSAUR |         2006
       2 | ACE GOLDFINGER   |         2006
       3 | ADAPTATION HOLES |         2006
       4 | AFFAIR PREJUDICE |         2006
       5 | AFRICAN EGG      |         2006
       6 | AGENT TRUMAN     |         2006
       7 | AIRPLANE SIERRA  |         2006
       8 | AIRPORT POLLOCK  |         2006
       9 | ALABAMA DEVIL    |         2006
      10 | ALADDIN CALENDAR |         2006

To get the 10 rows to display on the second page, we use the OFFSET 10 clause to skip the 10 rows on the first page and use FETCH FIRST 10 ROWS ONLY to limit the query to return a maximum of 10 rows using. You can get all the rows of the second page using a statement like this:

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

 film_id |        title        | release_year
---------+---------------------+--------------
      11 | ALAMO VIDEOTAPE     |         2006
      12 | ALASKA PHANTOM      |         2006
      13 | ALI FOREVER         |         2006
      14 | ALICE FANTASIA      |         2006
      15 | ALIEN CENTER        |         2006
      16 | ALLEY EVOLUTION     |         2006
      17 | ALONE TRIP          |         2006
      18 | ALTER VICTORY       |         2006
      19 | AMADEUS HOLY        |         2006
      20 | AMELIE HELLFIGHTERS |         2006

Similarly, you can use the following statement to get all the rows of the third page:

SELECT
  film_id,
  title,
  release_year
FROM film
ORDER BY film_id
OFFSET 20
FETCH FIRST 10 ROWS ONLY;

Here, the OFFSET 20 indicates to skip the 20 rows of the first two pages, and the FETCH FIRST 10 ROWS ONLY limits this query to return a maximum of 10 rows.

Conclusion

In this article, you learned how to use PostgreSQL FETCH clauses to limit the number of rows returned by a SELECT statement.

1. The FETCH and ORDER BY are used to solve the TOP N problem.
2. The FETCH, OFFSET and ORDER BY are used to solve paginated queries.