PostgreSQL LIMIT - Limit the number of rows returned

When you use the SELECT statement to query data from a large table, returning all the rows that may cause system overload. So, you want to return only a few rows at a time.

In PostgreSQL, you can use the LIMIT clause in the SELECT statement to limit the number of rows returned.

Note that although the LIMIT clause is supported by many database systems, it is not a part of SQL standard. To comply with the SQL standard, you can use the FETCH clause to accomplish the same thing.

PostgreSQL `LIMIT` Syntax

The PostgreSQL LIMIT is an optional clause of a SELECT statement to limit the number of rows returned by a query.

This is the syntax of the LIMIT clause:

LIMIT rows_count;

Here, the rows_count specify the maximum number of rows to return. For example, LIMIT 10 means to return up to 10 rows.

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

SELECT column_list
FROM table_name
[other_clauses]
LIMIT 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 LIMIT 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 LIMIT clause in the SELECT statement, as follows:

SELECT column_list
FROM table_name
ORDER BY ...
LIMIT rows_count OFFSET skipped_rows;

For example:

The first page can use LIMIT 10 OFFSET 0 that indicates that a maximum of 10 rows are returned.
The second page can use LIMIT 10 OFFSET 10 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 LIMIT 10 OFFSET 20 that indicates that a maximum of 10 rows are returned after skipping the 20 rows of the first two pages.
And so on…

PostgreSQL `LIMIT` 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 `LIMIT` 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 LIMIT clause:

SELECT
  film_id,
  title,
  release_year
FROM film
ORDER BY film_id
LIMIT 5;

 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 with PostgreSQL `LIMIT`

Typically, you often use the LIMIT 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 LIMIT 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
LIMIT 10;

The query results are as follows:

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

Example of paging query using PostgreSQL `LIMIT` 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 LIMIT 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
LIMIT 10;

Here, in order to keep the order of all pagination consistent, we use ORDER BY film_id to sort the films by film_id, and use LIMIT 10 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 LIMIT 10 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
LIMIT 10;

 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
LIMIT 10;

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

Conclusion

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

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

PostgreSQL LIMIT Syntax

PostgreSQL LIMIT Examples

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

Get the first N rows with PostgreSQL LIMIT