---
title: "Understanding OFFSET in PostgreSQL (With Examples)"
description: "OFFSET allows you to skip a certain number of rows in PostgreSQL before returning the result set. Learn how to use it with practical examples."
section: "Postgres basics"
---

> **TimescaleDB is now Tiger Data.**

*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](https://www.timescale.com/learn/understanding-limit-in-postgresql-with-examples) 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;
`

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;`


### 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
);`

`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');`


### 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;`


**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`


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`](https://www.timescale.com/learn/understanding-limit-in-postgresql-with-examples) 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;
`


**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
`

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;
`



**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
`

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`, [<u>check out the PostgreSQL documentation</u>](https://www.postgresql.org/docs/current/queries-limit.html) and head to our [<u>Postgres basics section</u>](https://www.timescale.com/learn/postgres-basics) to read more about other PostgreSQL clauses and how to use them.