PostgreSQL OFFSET

Introduction to PostgreSQL OFFSET

PostgreSQL offset is used to skip rows before returning a result of the query; suppose we have 100 records in the employee table, and we need to fetch the last 50 records from the table at that time we used to offset. The offset will skip the first 50 records and display the last 50 records as required. Offset is very important in PostgreSQL to skip rows before returning a result of a query. If we use offset value is zero, it will return the same value; the offset condition is not used in this type of scenario.

Syntax

Below is the syntax of the PostgreSQL offset is as follows.

Syntax #1 – PostgreSQL offset clause

select column_name1, …, column_nameN from table_name OFFSET N (Number of rows that we have skipping in query result)

select * (select all table columns) from table_name OFFSET N (Number of rows that we have skipping in query result)

Syntax #2 – Offset clause using limit clause

select column_name1, …, column_nameN from table_name OFFSET N (Number of rows that we have skipping in query result) LIMIT N

select * (select all table columns) from table_name OFFSET N (Number of rows that we have skipping in query result) LIMIT N

Syntax #3 –  PostgreSQL Offset using order by clause

select column_name1, …, column_nameN from table_name ORDER BY column_name OFFSET N (Number of rows that we have skipping in query result)

select column_name1, …, column_nameN from table_name ORDER BY column_name OFFSET N (Number of rows that we have skipping in query result) LIMIT N

select column_name1, …, column_nameN from table_name ORDER BY column_name DESC OFFSET N (Number of rows that we have skipping in query result)

select column_name1, …, column_nameN from table_name ORDER BY column_name ASC OFFSET N (Number of rows that we have skipping in query result)

Parameter PostgreSQL OFFSET

Below is the parameter description of the above syntax are as follows:

• Select: Select statement is used to select no rows using the offset clause.
• Column_name1 to column_nameN: We have selected a column to fetch data from the table using the offset clause.
• From: Keyword used to select the specified table to fetch data using the offset clause.
• Table name: Table used to fetch a specified record from the table using an offset clause.
• Asterisk (*): Asterisk used to select all columns from the table to fetch data.
• Offset N: Offset clause used in PostgreSQL to skip the rows before returning date.
• Limit N: The limit clause is used with the offset clause in PostgreSQL to select a specified number of rows from the table.
• Order by: Order by clause used with offset clause to fetch a record in ascending or descending order.
• ASC: Fetch data in ascending order by using order by an OFFSET clause in PostgreSQL.
• DESC: Fetch data in descending order by using order by an OFFSET clause in PostgreSQL.

How does OFFSET Clause work in PostgreSQL?

• Offset is used to skip rows before returning a result of the query.
• Suppose we have 1000 records in the student table, and we need to fetch the last 100 records from the table at that time we used offset in PostgreSQL.
• Offset will skip the first 900 records and display the last 100 records as required.
• Offset is very important to skip rows before returning a result of a query.
• If we have using offset value is zero, then it will return the same value; the offset condition is not used at this type of scenario in PostgreSQL.

Examples to Implement PostgreSQL OFFSET

Below is the example of implementing offset are as follows. We have using an employee table to describe the example of offset in PostgreSQL.

Example #1

Employee table to describe the example of offset in PostgreSQL.

Code:

testing=#  select * from employee;

Output:

Example #2

Example of offset by fetching data from all columns and skipping the first three columns. In the below example, we have fetching records from all columns and skipping the first three rows using offset.

Code:

testing=# select * from employee offset 3;

Output:

Example #3

Example of offset by fetching data from a specified column and skipping the first four rows. In the below example, we have fetching records from specified columns and retrieving data only from four columns using PostgreSQL limits.

Code:

testing=# select emp_id, emp_name, emp_address emp_salary from employee OFFSET 4;

Output:

Example #4

offset clause by using a limit clause to fetch two records and skipping the first three rows. In the below example, we have to retrieve data from all columns and skipping the first three rows. We have used a limit clause with the offset clause.

Code:

testing=# select * from employee offset 3 limit 2;

Output:

Example #5

Offset clause by using a limit clause to fetch data from the specified column and skipping the first three rows. In the below example, we have retrieved data from the specified column and skipped the first three rows using the limit and offset clause.

Code:

testing=# select emp_id, emp_name, emp_address emp_salary from employee OFFSET 3 LIMIT 4;

Output:

Example #6 – Offset using order by clause

1. Fetch the data in ascending order by using order by.

Code:

testing=# select emp_id, emp_name, emp_address emp_salary from employee order by emp_id ASC OFFSET 4 LIMIT 3;

Output:

2. Fetch the data in descending order by using order by.

Code:

testing=# select emp_id, emp_name, emp_address emp_salary from employee order by emp_id DESC OFFSET 4 LIMIT 3;

Output:

Advantages of using OFFSET in PostgreSQL

Below are the advantages of offset are as follows.

• Using the offset clause, we can skip the rows before returning an output.
• The offset clause is used with the limit clause.
• The offset clause is used with an order by clause to fetch a record in ascending and descending order.
• Offset is very important in PostgreSQL.
• Offset clause is skip specified rows and return the result of the query.

Conclusion

PostgreSQL offset clause is essential in PostgreSQL to skip the number of rows before returning the query’s output. The offset clause is used with a limit clause to fetch the specific number of rows. Using order by clause, we can fetch data in ascending and descending order in the offset clause.

Recommended Articles

This is a guide to PostgreSQL OFFSET. Here we discuss syntax, parameter, advantages, examples to implement with proper codes and outputs. You can also go through our other related articles to learn more –

1. PostgreSQL Constraints
2. PostgreSQL Architecture
3. PostgreSQL Cross Join
4. Indexes in PostgreSQL
5. How to Work PostgreSQL Alias?
6. PostgreSQL LIMIT | How to Works?