PostgreSQL LIMIT

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 used in the SELECT statement to get a subset of rows returned by the query. The following is the common syntax of the LIMIT clause:

PostgreSQL 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 n rows, you use OFFSET clause followed by the LIMIT clause as follows:

PostgreSQL first skips m rows before returning n rows generated by the query. If m is zero, PostgreSQL will behave like without the OFFSET clause.

Because the order of the rows in the database table is unknown and 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.

PostgreSQL LIMIT examples

Let’s take some examples of using PostgreSQL LIMIT clause to understand it better.

To get the first 5 films ordered by film_id, you use the following query:

To retrieve 4 films starting from the third one ordered by film_id, you use both LIMIT and OFFSET clauses as follows:

You 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 LIMIT clause to get 10 films order by the rental rate in descending order.

The result of the query is as follows:

In this tutorial, we have shown you how to use the PostgreSQL LIMIT clause to retrieve a subset of rows returned by a query.