In PostgreSQL, the OFFSET clause is used to skip some records before returning the result set of a query. Mostly, OFFSET clauses are used in conjunction with LIMIT clauses to skip a subset of records before returning the result set of the LIMIT query.

This blog post will demonstrate various use cases of the OFFSET clause in Postgres via practical examples. So, let’s get started.

How Does OFFSET Clause Work in Postgres?

To use the OFFSET clause in Postgres, users must follow the below-given syntax:

SELECT col_list
FROM tbl_name
OFFSET num;

Let’s comprehend the above syntax stepwise:

• col_list represents the columns to be fetched.
• tbl_name is a table from which the records will be fetched.
• OFFSET is a clause that will skip a subset of records.
• num represents the number of records to be skipped.

Example 1: Understanding Basics of the OFFSET Clause

We have created an “article_details” table whose content is shown below:

SELECT * FROM article_details;

The article_details table has twelve records. Suppose the user wants to skip the first five records from the selected table, i.e., “article_details”. For this purpose, the user can utilize the OFFSET clause as follows:

SELECT * FROM article_details
OFFSET 5;

The output shows that the OFFSET clause has skipped the five records and retrieves the remaining records from the “article_details” table.

Example 2: OFFSET With ORDER BY Clause in Postgres

What if the user wants to skip the last five records? Well! In such scenarios, users can use the ORDER BY clause with the DESC order:

SELECT * FROM article_detail
ORDER BY article_id DESC
OFFSET 5;

The output proves that the OFFSET clause skipped the last five records and retrieved the remaining records.

Example 3: How to Use OFFSET Clause With LIMIT Clause in Postgres?

Use the LIMIT clause in conjunction with the OFFSET clause to skip a subset of records before returning the LIMIT query:

SELECT * FROM article_detail
ORDER BY article_id
LIMIT 5 OFFSET 2;

In the above query:

• The “LIMIT 5” clause is used to fetch only five records.
• The “OFFSET 2” clause is used to skip the first two records before retrieving the result set of the limit clause:

The output proves that the OFFSET clause skipped the first two records and the LIMIT clause fetched the next five records from the targeted table.

That’s all from this Postgres guide!

Conclusion

In PostgreSQL, the OFFSET clause is used to skip some records before returning the result set of a query. By default, the OFFSET clause skipped the records from the top; however, if you have to skip the records from the bottom, you must use the WHERE clause with the DESC option. This blog post demonstrated various use cases of the OFFSET clause via practical examples.