offset and limit in Postgres

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this article we’ll discuss how to use the offset and limit in Postgres. We’ll quickly go over some prerequisites, then we’ll explain what they do, followed by how to use them.

Prerequisite

Ensure to install and configure the following for your OS:

Basic PostgreSQL is required, to be able to follow along with the tutorial.

What is offset and limit in Postgres

Postgres OFFSET option let’s you control how many table rows to skip from the start of the table. The LIMIT option lets you set the max number of table rows to be returned by a SELECT operation.

Basically the LIMIT option as the name implies, will only retrieve number of rows as specified after the keyword LIMIT.

The OFFSET defines the number of records to skip from the returned result set.

Below is the basic syntax of the OFFSET and LIMIT options.

1
2
3
4
5
6
7
SELECT select_fields

    FROM <table_name>

    [ ORDER BY ... ]

    [ LIMIT { NUMBER | ALL } ] [ OFFSET NUMBER ]
  • In the above statement we perform a SELECT operation against selected fields (select_fields).

  • <table_name> – This is the name of the table.

  • We can use an optional clause ORDER BY to further refine the returned result set in a more organized manner.

  • Finally, we perform the LIMIT and OFFSET option.

Creating Sample Dataset

In this section we will be creating our sample data set that we will be using for our demo.

To do this follow the steps in sequence.

  • First, login to your Postgres shell.

  • Second, we create a database using the command CREATE DATABASE

1
CREATE DATABASE grocery;
  • Third, we connect to the database via the command \c grocery.
  • Fourth, we create a table named items with the following structure.
1
2
3
4
5
CREATE TABLE IF NOT EXISTS item (
    id SERIAL PRIMARY KEY,
    item_name CHARACTER VARYING(100),
    item_quantity INTEGER
    );
  • Finally, we insert some dummy records in the table using the following syntax.
1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO item (id, item_name, item_quantity)
VALUES
(1,'eggs',12),
(2,'carrots',10),
(3,'milk',2),
(4,'juice drink',3),
(5,'jam',5),
(6,'apples',12),
(7,'hand soap',4),
(8,'toilet paper',12),
(9,'Bottled water',35),
(10,'Disinfectant',12);

Our table should look something like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
 id |   item_name   | item_quantity
----+---------------+---------------
  1 | eggs          |            12
  2 | carrots       |            10
  3 | milk          |             2
  4 | juice drink   |             3
  5 | jam           |             5
  6 | apples        |            12
  7 | hand soap     |             4
  8 | toilet paper  |            12
  9 | Bottled water |            35
 10 | Disinfectant  |            12
(10 rows)

LIMIT example

In this section we will be using the LIMIT clause to limit the records from the returned result set.

1
SELECT * FROM item LIMIT 5;

In the above syntax, we limit the returned result set to five (5).

This is the output:

1
2
3
4
5
6
7
8
 id |  item_name  | item_quantity
----+-------------+---------------
  1 | eggs        |            12
  2 | carrots     |            10
  3 | milk        |             2
  4 | juice drink |             3
  5 | jam         |             5
(5 rows)

OFFSET example

In this section we will be using the OFFSET clause to only present the remaining records while living out the specified number after the OFFSET clause.

1
SELECT * FROM item OFFSET 4;

In the above syntax we wanted to offset the for rows and only return the succeeding data after the four (4) rows/records.

This is the output:

1
2
3
4
5
6
7
8
9
 id |   item_name   | item_quantity
----+---------------+---------------
  5 | jam           |             5
  6 | apples        |            12
  7 | hand soap     |             4
  8 | toilet paper  |            12
  9 | Bottled water |            35
 10 | Disinfectant  |            12
(6 rows)

Conclusion

Thank you for joining our tutorial on the OFFSET and LIMIT in Postgres. Both options are pretty self explanatory in what they do. Offset let’s you set an offset in your query, or skip a certain number of entries. The limit option lets you set a maximum number of rows you want returned in your query.

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.