PostgreSQL – LIMIT clause

The PostgreSQL LIMIT clause is used to get a subset of rows generated by a query. It is an optional clause of the SELECT statement.

Syntax: SELECT * FROM table_name LIMIT n;

Now let’s analyze the syntax above:

• The above syntax returns “n” no. of query results.
• If “n” is skipped or equal to NULL it returns all the query results.

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:
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.

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

Output:

Example 2:
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.

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

Output: