Summary: this tutorial shows you how to use PostgreSQL LIMIT clause to get a subset of rows generated by a query.
Introduction to PostgreSQL LIMIT clause
PostgreSQL LIMIT
is an optional clause of the SELECT
statement that gets a subset of rows returned by a query.
The following illustrates the syntax of the LIMIT
clause:
1 2 3 4 5 | SELECT * FROM table_name LIMIT n; |
The statement returns n
rows generated by the query. If n
is zero or NULL
, the query returns the same result set as omitting the LIMIT
clause.
In case you want to skip a number of rows before returning the n
rows, you use OFFSET
clause placed after the LIMIT
clause as the following statement:
1 2 3 4 5 | SELECT * FROM table LIMIT n OFFSET m; |
The statement first skips m
rows before returning n
rows generated by the query. If m
is zero, the statement will work like without the OFFSET
clause.
Because the order of the rows in the database table is unpredictable, when you use the LIMIT
clause, you should always use the ORDER BY
clause to control the order of rows. If you don’t do so, you will get an unpredictable result set.
If you use a large OFFSET
, it might not be efficient because PostgreSQL still has to calculate the rows skipped by the OFFSET
inside the database server, even though the skipped rows are not returned.
PostgreSQL LIMIT examples
Let’s take some examples to have a better understanding of using PostgreSQL LIMIT
clause. We will use the film
table in the sample database for the demonstration.
To get the first 5 films ordered by film_id
, you use the following query:
1 2 3 4 5 6 7 8 9 | SELECT film_id, title, release_year FROM film ORDER BY film_id LIMIT 5; |
To retrieve 4 films starting from the third one ordered by film_id
, you use both LIMIT
and OFFSET
clauses as follows:
1 2 3 4 5 6 7 8 9 | SELECT film_id, title, release_year FROM film ORDER BY film_id LIMIT 4 OFFSET 3; |
We often use the LIMIT
clause to get the number of highest or lowest items in a table. For example, to get top 10 most expensive films, we can use the
For example, to get top 10 most expensive films, you sort the film by the rental rate in descending order and use the LIMIT
clause to get first 10 films. The following query illustrates the idea:
1 2 3 4 5 6 7 8 9 | SELECT film_id, title, rental_rate FROM film ORDER BY rental_rate DESC LIMIT 10; |
The result of the query is as follows:
In this tutorial, you have learned how to use the PostgreSQL LIMIT
clause to retrieve a subset of rows returned by a query.