PostgreSQL: How to Select Range of Rows from Table

Often in PostgreSQL you may want to select a specific range of rows from a table.

You can use the following basic syntax to do so:

SELECT *
FROM athletes
LIMIT 5 OFFSET 2;

This particular example will select 5 rows from the table named athletes, while skipping the first 2 rows.

Thus, this particular example will return the rows in the range 3 through 7.

Note the following:

• LIMIT specifies the total number of rows to return.
• OFFSET specifies the total number of rows to skip from the beginning.

If you don’t specify a value for OFFSET, then the first N rows that you specify in the LIMIT statement will be returned from the beginning of the table.

The following example shows how to use this syntax in practice.

Example: How to Select Range of Rows from a Table in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  position TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Celtics', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Nuggets', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);

-- view all rows in table
SELECT * FROM athletes;

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         1 | Mavs    |     22 |       5
         2 | Hawks   |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 | Mavs    |     31 |      14
         6 | Mavs    |     16 |       3
         7 | Celtics |     10 |       5
         8 | Celtics |     14 |      13
         9 | Nuggets |     20 |       8
        10 | Hawks   |     26 |       3

We can use the following syntax to select rows 1 through 5 of the table:

SELECT *
FROM athletes
LIMIT 5;

 athleteid | team  | points | assists 
-----------+-------+--------+---------
         1 | Mavs  |     22 |       5
         2 | Hawks |     30 |      12
         3 | Kings |     12 |       3
         4 | Kings |     25 |       9
         5 | Mavs  |     31 |      14

Notice that the first 5 rows from the table are returned.

We can also use the following syntax to select rows 3 through 7:

SELECT *
FROM athletes
LIMIT 5 OFFSET 2;

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 | Mavs    |     31 |      14
         6 | Mavs    |     16 |       3
         7 | Celtics |     10 |       5

Notice that rows 3 through 7 are returned.

Feel free to specify whatever values you would like with the LIMIT and OFFSET statements to select a specific range of rows from your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to INSERT If Not Exists Already
PostgreSQL: How to Calculate Difference Between Rows