Understanding OFFSET in PostgreSQL (With Examples)

Written by Team Timescale

In PostgreSQL or TimescaleDB, the OFFSET clause allows you to skip a certain number of rows before returning the result set. It is particularly useful for implementing pagination in your applications.

In this post, we'll explore the OFFSET clause, understand its importance, and see how it can be used with practical examples.

What Is the OFFSET Clause?

The OFFSET clause in PostgreSQL is used to skip a specified number of rows before starting to return rows from the query. It is commonly used alongside the LIMIT clause to paginate results.

PostgreSQL OFFSET Syntax

The basic syntax of the OFFSET clause is as follows:

SELECT column1, column2, ... FROM table OFFSET number_of_rows_to_skip; Copy

You can also combine OFFSET with LIMIT to paginate results:

SELECT column1, column2, ... FROM table LIMIT number_of_rows OFFSET number_of_rows_to_skip;Copy

Example table

Let's consider a table weather_data with the following structure and data:

CREATE TABLE weather_data (     id SERIAL PRIMARY KEY,     device_id VARCHAR(50),     temperature NUMERIC,     humidity NUMERIC,     wind_speed NUMERIC,     recorded_at TIMESTAMP );Copy

INSERT INTO weather_data (device_id, temperature, humidity, wind_speed, recorded_at) VALUES ('device_1', 22.5, 55.0, 12.5, '2023-01-15 08:00:00'), ('device_2', 21.0, 60.0, 10.0, '2023-01-15 08:05:00'), ('device_1', 23.0, 57.0, 11.0, '2023-01-15 08:10:00'), ('device_2', 19.5, 62.0, 8.0, '2023-01-15 08:15:00'), ('device_1', 20.0, 59.0, 9.5, '2023-01-15 08:20:00'), ('device_3', 24.0, 54.0, 13.0, '2023-01-15 08:25:00'), ('device_2', 18.5, 63.0, 7.5, '2023-01-15 08:30:00'), ('device_3', 22.0, 55.5, 12.0, '2023-01-15 08:35:00'), ('device_1', 21.5, 58.0, 10.5, '2023-01-15 08:40:00'), ('device_3', 23.5, 53.0, 13.5, '2023-01-15 08:45:00');Copy

Practical examples using the OFFSET clause

Let's dive into some examples to see the OFFSET clause in action.

Example 1: Using OFFSET to skip rows

Suppose we want to skip the first five rows and retrieve the remaining rows from the weather_data table. We can use the OFFSET clause to achieve this:

SELECT id, device_id, temperature, humidity, wind_speed, recorded_at FROM weather_data OFFSET 5;Copy

Result:

 id | device_id | temperature | humidity | wind_speed |     recorded_at       ----+-----------+-------------+----------+------------+---------------------   6 | device_3  |        24.0 |     54.0 |       13.0 | 2023-01-15 08:25:00   7 | device_2  |        18.5 |     63.0 |        7.5 | 2023-01-15 08:30:00   8 | device_3  |        22.0 |     55.5 |       12.0 | 2023-01-15 08:35:00   9 | device_1  |        21.5 |     58.0 |       10.5 | 2023-01-15 08:40:00  10 | device_3  |        23.5 |     53.0 |       13.5 | 2023-01-15 08:45:00Copy

This query skips the first five rows and returns the remaining rows from the weather_data table.

Example 2: Combining OFFSET with LIMIT

Now, let's say we want to implement pagination by retrieving five rows at a time. We can combine OFFSET with LIMIT to achieve this. For example, to get the second page of results (rows 6 to 10), we can use:

SELECT id, device_id, temperature, humidity, wind_speed, recorded_at FROM weather_data LIMIT 5 OFFSET 5; Copy

Result:

id | device_id | temperature | humidity | wind_speed |     recorded_at       ----+-----------+-------------+----------+------------+---------------------   6 | device_3  |        24.0 |     54.0 |       13.0 | 2023-01-15 08:25:00   7 | device_2  |        18.5 |     63.0 |        7.5 | 2023-01-15 08:30:00   8 | device_3  |        22.0 |     55.5 |       12.0 | 2023-01-15 08:35:00   9 | device_1  |        21.5 |     58.0 |       10.5 | 2023-01-15 08:40:00  10 | device_3  |        23.5 |     53.0 |       13.5 | 2023-01-15 08:45:00 Copy

This query returns rows 6 to 10 from the weather_data table.

Example 3: Using OFFSET with ORDER BY

It's common to use OFFSET with ORDER BY to ensure the rows are retrieved in a specific order before skipping and limiting the rows. For instance, to get the latest five records after skipping the first five records, we can use:

SELECT id, device_id, temperature, humidity, wind_speed, recorded_at FROM weather_data ORDER BY recorded_at DESC LIMIT 5 OFFSET 5; Copy

Result:

 id | device_id | temperature | humidity | wind_speed |     recorded_at       ----+-----------+-------------+----------+------------+---------------------   5 | device_1  |        20.0 |     59.0 |        9.5 | 2023-01-15 08:20:00   3 | device_1  |        23.0 |     57.0 |       11.0 | 2023-01-15 08:10:00   2 | device_2  |        21.0 |     60.0 |       10.0 | 2023-01-15 08:05:00   1 | device_1  |        22.5 |     55.0 |       12.5 | 2023-01-15 08:00:00 Copy

This query sorts the rows by recorded_at in descending order, then skips the first five rows and returns the next five rows.

Conclusion

The OFFSET clause is a powerful tool in PostgreSQL for controlling the starting point of your result set. By mastering it, you can write more effective queries that better serve your application's needs and improve user experience. To learn more about OFFSET, check out the PostgreSQL documentation and head to our Postgres basics section to read more about other PostgreSQL clauses and how to use them.