Records range (OFFSET/LIMIT)

Contents

Records range (OFFSET/LIMIT)#

Suppose you need to select a specific range from your table. This can be achieved using the OFFSET and LIMIT functions:

  • The OFFSET <N> function instructs the query to ignore the first N records.

  • The LIMIT <N> function limits the query to return only the top N records.

Consider example here is some people and they order of registration in some service.

%%bash
docker run --rm -d\
    -e POSTGRES_PASSWORD=docker_app \
    --name records_range_example \
    postgres:15.4 &> /dev/null
sleep 5
docker exec -i records_range_example psql -U postgres -d postgres

CREATE TABLE tab (
    registration_index SERIAL,
    name TEXT
);

INSERT INTO tab (name) VALUES
('Emma'),
('Olivia'),
('Ava'),
('Isabella'),
('Sophia'),
('Mia'),
('Charlotte'),
('Amelia'),
('Harper'),
('Evelyn'),
('Abigail'),
('Emily'),
('Elizabeth'),
('Mila'),
('Ella'),
('Avery'),
('Sofia'),
('Camila'),
('Aria'),
('Scarlett');

SELECT * FROM tab;

CREATE TABLE
INSERT 0 20
 registration_index |   name    
--------------------+-----------
                  1 | Emma
                  2 | Olivia
                  3 | Ava
                  4 | Isabella
                  5 | Sophia
                  6 | Mia
                  7 | Charlotte
                  8 | Amelia
                  9 | Harper
                 10 | Evelyn
                 11 | Abigail
                 12 | Emily
                 13 | Elizabeth
                 14 | Mila
                 15 | Ella
                 16 | Avery
                 17 | Sofia
                 18 | Camila
                 19 | Aria
                 20 | Scarlett
(20 rows)

Note Don’t forget to stop the docker container when you’re finished playing.

!docker stop records_range_example

records_range_example

OFFSET#

Let’s examine the OFFSET command individually. By utilizing the OFFSET 10 within the query, we instruct it to disregard the first 10 records in the output.

%%bash
docker exec -i records_range_example psql -U postgres -d postgres
SELECT * FROM tab OFFSET 10;

 registration_index |   name    
--------------------+-----------
                 11 | Abigail
                 12 | Emily
                 13 | Elizabeth
                 14 | Mila
                 15 | Ella
                 16 | Avery
                 17 | Sofia
                 18 | Camila
                 19 | Aria
                 20 | Scarlett
(10 rows)

LIMIT#

Let’s discuss the LIMIT command separately. By applying LIMIT 10 to the query, we instruct it to return only the first 10 records.

%%bash
docker exec -i records_range_example psql -U postgres -d postgres
SELECT * FROM tab LIMIT 10;

 registration_index |   name    
--------------------+-----------
                  1 | Emma
                  2 | Olivia
                  3 | Ava
                  4 | Isabella
                  5 | Sophia
                  6 | Mia
                  7 | Charlotte
                  8 | Amelia
                  9 | Harper
                 10 | Evelyn
(10 rows)

Range#

Finally, by using OFFSET 3 LIMIT 10, we retrieve records from the output order within the range of 4 to 13. This range is determined by adding an offset of 3 to the starting record number (1+3=4) and limiting the result to a maximum of 10 records (3+10=13).

%%bash
docker exec -i records_range_example psql -U postgres -d postgres
SELECT * FROM tab OFFSET 3 LIMIT 10;

 registration_index |   name    
--------------------+-----------
                  4 | Isabella
                  5 | Sophia
                  6 | Mia
                  7 | Charlotte
                  8 | Amelia
                  9 | Harper
                 10 | Evelyn
                 11 | Abigail
                 12 | Emily
                 13 | Elizabeth
(10 rows)
Contents