Introduction to PostgreSQL LIMIT clause

PostgreSQLLIMIT is an optional clause of theSELECT statement that constrains the number of rows returned by the query.

Here’s the basic syntax of theLIMIT clause:

SELECT  select_listFROM  table_nameORDER BY  sort_expressionLIMIT  row_count;

The statement returnsrow_count rows generated by the query. If therow_count is zero, the query returns an empty set. If therow_count isNULL, the query returns the same result set as it does not have theLIMIT clause.

If you want to skip a number of rows before returning the row_count rows, you can useOFFSET clause placed after theLIMIT clause:

SELECT  select_listFROM  table_nameORDER BY  sort_expressionLIMIT  row_countOFFSET  row_to_skip;

The statement first skipsrow_to_skip rows before returningrow_count rows generated by the query.

If therow_to_skip is zero, the statement will work like it doesn’t have theOFFSET clause.

It’s important to note that PostgreSQL evaluates theOFFSET clause before theLIMIT clause.

PostgreSQL stores rows in a table in an unspecified order, therefore, when you use theLIMIT clause, you should always use the ORDER BY clause to control the row order.

If you don’t use theORDER BY clause, you may get a result set with the rows in an unspecified order.

PostgreSQL LIMIT clause examples

Let’s take some examples of using the PostgreSQLLIMIT clause. We will use thefilm table in thesample database for the demonstration.

1) Using PostgreSQL LIMIT to constrain the number of returned rows

The following statement uses theLIMIT clause to get the first five films sorted byfilm_id:

SELECT  film_id,  title,  release_yearFROM  filmORDER BY  film_idLIMIT  5;

Output:

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(5 rows)

How it works.

• First, sort films by thefilm_id ascending order using theORDER BY film_id clause.
• Second, take 5 films from the top using theLIMIT 5 clause.

2) Using the LIMIT clause with the OFFSET clause example

To retrieve 4 films starting from the fourth one ordered byfilm_id, you can use bothLIMIT andOFFSET clauses as follows:

SELECT  film_id,  title,  release_yearFROM  filmORDER BY  film_idLIMIT 4 OFFSET 3;

Output:

film_id |      title       | release_year---------+------------------+--------------       4 | Affair Prejudice |         2006       5 | African Egg      |         2006       6 | Agent Truman     |         2006       7 | Airplane Sierra  |         2006(4 rows)

How it works.

• First, sort films by film id in ascending order.
• Second, skip the first three rows using theOFFSET 3 clause.
• Second, take the next four rows using theLIMIT 4 clause.

3) Using LIMIT OFFSET to get top/bottom N rows

Typically, you often use theLIMIT clause to select rows with the highest or lowest values from a table.

The following example uses theLIMIT clause to retrieve the top 10 most expensive films by rental rate:

SELECT  film_id,  title,  rental_rateFROM  filmORDER BY  rental_rate DESCLIMIT  10;

Output:

film_id |        title        | rental_rate---------+---------------------+-------------      13 | Ali Forever         |        4.99      20 | Amelie Hellfighters |        4.99       7 | Airplane Sierra     |        4.99      10 | Aladdin Calendar    |        4.99       2 | Ace Goldfinger      |        4.99       8 | Airport Pollock     |        4.99      98 | Bright Encounters   |        4.99     133 | Chamber Italian     |        4.99     384 | Grosse Wonderful    |        4.99      21 | American Circus     |        4.99(10 rows)

How it works.

• First, sort all the films by rental rates from high to low using theORDER BY rental_rate DESC clause.
• Second, take only 10 rows from the top using theLIMIT 10 clause.

Summary

• Use the PostgreSQLLIMIT OFFSET clause to retrieve a subset of rows returned by a query.

Last updated onApril 19, 2024

On this page

• Introduction to PostgreSQL LIMIT clause
• PostgreSQL LIMIT clause examples
• Summary