LIMIT in Postgres

Introduction

When you perform a query in PostgreSQL, you may want to restrict the number of records that are returned. You might only need the first ten records, or you may want to return subsets of a result set to handle pagination of results in your code. Regardless of the particular use case, it’s easy to retrieve subsets of results using LIMIT in PostgreSQL. In this article, we’ll provide an overview of the LIMIT clause and look at a few examples of its use.

Prerequisite

If you’d like to follow along with the examples in this tutorial, make sure that you have PostgreSQL installed and configured on your system.

What is Postgres LIMIT?

The LIMIT clause is an optional component of the SELECT statement. It will return a specified subset of the rows that would be returned from the query.

Let’s look at the basic syntax of the Postgres LIMIT clause:

1
2
3
4
5
SELECT
<column>
FROM
<table_name>
LIMIT x;

Notice that this query returns x rows as a result. IF we set the value of x to ‘0’, the query would return an empty set. If we set the value of the LIMIT clause to NULL, the query will return the complete result set as if the LIMIT clause was not used at all.

There are many situations where we might need to skip certain rows before returning the ‘x’ rows specified in the LIMIT clause. The pagination use case we mentioned earlier is a good example– after the first page of results, we’d need to skip a certain number of rows for each subsequent page. To accomplish this, we can append another helper clause named OFFSET to our SELECT statement. We append the OFFSET after the LIMIT clause.

The basic syntax is shown below:

1
2
3
SELECT col1, col2, col3,...
FROM <table_name>
LIMIT [x NUMBER OF ROWS] OFFSET [ROW NUMBER]

Creating sample dataset

Now that we’ve discussed the basics of LIMIT in Postgres, let’s create a sample dataset that we can use throughout our examples.

First, let’s log in to the Postgres shell and create a database named ’employeedb’. We’ll use the following command:

1
CREATE DATABASE employeedb;

Then we’ll create a table within the ’employeedb’ and name it ’employee’. Our table will have the following structure.

1
2
3
4
5
6
CREATE TABLE employee (
    id INT PRIMARY KEY,
    first_name VARCHAR (50),
    last_name VARCHAR (50),
    department VARCHAR (20)
);

Now, let’s insert a couple of records into the employee table that we just created:

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO employee (id, first_name, last_name, department)
VALUES
    (1, 'darryl', 'ong', 'ict'),
    (2, 'bugoy', 'drilon', 'engineering'),
    (3, 'michael', 'pangilinan', 'engineering'),
    (4, 'KC', 'tandingan', 'operations'),
    (5, 'marcelito', 'pomoy', 'operations'),
    (6, 'sarah', 'geronimo', 'ict'),
    (7, 'jessy', 'garner', 'operations'),
    (8, 'denver', 'looney', 'payroll'),
    (9, 'dax', 'prenson', 'payroll'),
    (10, 'antonio', 'bander', 'admin');

At this point, we should have a table that looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
id | first_name | last_name  | department
----+------------+------------+-------------
1 | darryl     | ong        | ict
2 | bugoy      | drilon     | engineering
3 | michael    | pangilinan | engineering
4 | KC         | tandingan  | operations
5 | marcelito  | pomoy      | operations
6 | sarah      | geronimo   | ict
7 | jessy      | garner     | operations
8 | denver     | looney     | payroll
9 | dax        | prenson    | payroll
10 | antonio    | bander     | admin
(10 rows)

Postgres LIMIT examples

In this section, we’ll show you how to use the clause named LIMIT in Postgres.

Let’s look at the following SELECT statement:

1
2
3
4
5
6
7
8
9
SELECT
id,
first_name,
last_name
FROM
employee
ORDER BY
id
LIMIT 5;

This query will select the columns ‘id’, ‘first_name’ and the ‘last_name’ from the table employee. The results are sorted based on the ‘id’ column. Finally, we limit the result to only five rows.

The output should look like the following:

1
2
3
4
5
6
7
8
id | first_name | last_name
----+------------+------------
1 | darryl     | ong
2 | bugoy      | drilon
3 | michael    | pangilinan
4 | KC         | tandingan
5 | marcelito  | pomoy
(5 rows)

Postgres LIMIT with OFFSET examples

The previous example showed a fairly basic implementation of the LIMIT clause. Now let’s try a more complex example that makes use of the OFFSET clause.

Let’s examine the following query:

1
2
3
4
5
6
7
8
9
SELECT
id,
first_name,
last_name
FROM
employee
ORDER BY
id
LIMIT 3 OFFSET 3;

The query shown above simply retrieves all the values from the specified columns; however, it only returns the result set that includes the 4th to the 6th row since we limited the result to ‘3’ and specified an offset of ‘3’.

The output should look something like this:

1
2
3
4
5
6
id | first_name | last_name
----+------------+-----------
4 | KC         | tandingan
5 | marcelito  | pomoy
6 | sarah      | geronimo
(3 rows)

Conclusion

When you execute a SELECT statement, you don’t always want the complete result set returned. The LIMIT Postgres clause can be added to a query to return just a subset of the full results. In this article, we looked at some typical examples of the LIMIT clause as part of a query. You can use these examples as a guide to restrict results in your own PostgreSQL queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.