PostgreSQL - LIMIT clause

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.