PostgreSQL Limit Clause

Let’s discuss the ins and outs of using the PostgreSQL LIMIT clause to limit the output to a specified value.

Although the LIMIT clause is an optional statement, it is very handy when you do not want a query to return thousands and thousands of records.

Basic Usage

The LIMIT clause is pretty straightforward. The general syntax for the query is:

SELECT select_lst FROM table LIMIT {number | ALL }

The Limit value is an integer value that specifies the row limit for the output.

For example, if a query returns 100 records and you set the limit as 5, only 5 rows are displayed.

NOTE: Unless the query itself returns fewer values than those specified in the LIMIT clause, the limit value always sets the return value.

If the LIMIT value is set to 0, the query will return an empty set. On the other hand, if no value is set for the LIMIT clause (NULL), the query will return the same values as it does not have a LIMIT clause specified.

PostgreSQL Limit Clause Examples

Let us illustrate how to use the LIMIT clause by examples.

Example 1

The first example shows a simple usage of the LIMIT clause. In this example, we will be using the sample sakila database.

Consider the film table; we can determine the number of records using the query:

SELECT count(*) FROM film;
count
-------
1000
(1 row)

In this example, the table contains 1000 records. Dumping all the records may not be very efficient. Instead, we can limit the output to only 10 records as:

SELECT film_id, title, release_year, rating FROM film LIMIT 10;

This command should return filtered information as shown in the table below:

Example 2

In most cases, when limiting the output, it is good to order by a specific column. Doing this allows you to get an understanding of the values returned.

For example, we can order the output in the previous example by the film_id.

An example query is below:

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

In this example, the results are organized by film_id in ascending order—unlike the previous example where the result was in random order.

Example 3

There is a friend to the LIMIT clause. The OFFSET clause is pretty similar to the LIMIT clause but only skips the specified number of rows.

For example, to start the result from the 100 row, we can set the offset as:

SELECT film_id, title, release_year, rating FROM film ORDER BY film_id OFFSET 99;

In this example, the result starts from the 100th row forward.

Similarly, it is good to order the results to follow a specific order.

Example 4

You can also use the LIMIT and OFFSET clauses in the same query. For example, consider the query below:

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

The order of the LIMIT and OFFSET is irrelevant as the OFFSET gets evaluated first then the LIMIT clause.

Example 5

As mentioned, you can use the LIMIT 0, which returns no records. However, it does return the columns as:

SELECT * FROM film ORDER BY film_id LIMIT 0;

Example 6

You can also use the LIMIT clause in conjunction with the ORDER BY to get a specific number of last elements.

For example, to get the last 5 elements in a table, we can set the ORDER BY clause to be DESC and LIMIT the value to 5 as:

SELECT film_id, title, release_year, rating FROM film ORDER BY film_id DESC LIMIT 5;

In this example, we should get the 5 last elements as:

Conclusion

In this quick guide, we discussed how the LIMIT and OFFSET clause works. We also used a few examples to illustrate how you can harness its features to get customized results.