PostgreSQL LIMIT clause (original) (raw)

Last Updated : 12 Jul, 2025

The **PostgreSQL LIMIT clause is a handy tool used to fetch a specific subset of rows returned by a query. This clause is optional and can be a powerful way to control the amount of data your query returns, especially when working with large datasets.

Let us better understand the **LIMIT Clause in **PostgreSQL from this article.

**Syntax

**SELECT * **FROM table_name **LIMIT n;

Parameters

Now let's analyze the syntax above:

• **SELECT * FROM table_name: This part of the query specifies the table from which you want to retrieve data.
• **LIMIT n: If "n" is skipped or equal to NULL it returns all the query results.

PostgreSQL LIMIT clause Examples

For the sake of this article we will be using the **sample DVD rental database, which is explained here and can be downloaded by clicking on this link. Now, let's look into a few examples.

**Example 1: Fetching the First 10 Films

In this example we will be using the LIMIT clause to get the first 10 films ordered by the "**film_id" from the "**film" table of our sample database.

**Query:

**SELECT film_id, title, rating **FROM film **ORDER BY film_id **LIMIT 10;

**Output:

**Explanation: This query will return the first 10 films based on their '**film_id'.

**Example 2: Fetching the Top 10 Most Expensive Films

In this example we will be using the LIMIT clause to get the top 10 expensive films ordered by the "**rental_rate" from the "**film" table of our sample database.

**Query:

**SELECT film_id, title, rental_rate **FROM film **ORDER BY rental_rate **DESC **LIMIT 10;

**Output:

**Explanation: This query will return the top 10 films with the highest rental rates.

Important Points About PostgreSQL LIMIT clause

• Without **ORDER BY, the rows returned by **LIMIT are not guaranteed to be in any specific order.
• When using **LIMIT with complex queries involving joins, **aggregations, or window functions, the position of the LIMIT clause can significantly impact the query result and performance. Always ensure that LIMIT is applied at the correct level of the query.
• The **LIMIT clause is often used in conjunction with the **OFFSET clause to paginate results. The **OFFSET clause skips a specified number of rows before beginning to return rows from the query.