PostgreSQL FETCH Clause

PostgreSQL FETCH Clause

The PostgreSQL FETCH clause is used to limit the number of rows returned by a query after a certain number of rows have been skipped. This clause is often used with the OFFSET clause to implement pagination in query results.

---

Syntax

SELECT column1, column2, ...
FROM table_name
OFFSET number_of_rows_to_skip
FETCH NEXT number_of_rows_to_return ROWS ONLY;

The FETCH clause has the following components:

• number_of_rows_to_skip: The number of rows to skip before starting to return rows.
• number_of_rows_to_return: The number of rows to return after skipping the specified number of rows.

---

Example PostgreSQL FETCH Clause Queries

Let's look at some examples of PostgreSQL FETCH clause queries:

1. Basic FETCH Example

SELECT first_name, last_name
FROM employees
OFFSET 0
FETCH NEXT 5 ROWS ONLY;

This query retrieves the first 5 rows from the employees table, skipping 0 rows.

2. FETCH with OFFSET

SELECT first_name, last_name
FROM employees
OFFSET 10
FETCH NEXT 5 ROWS ONLY;

This query retrieves 5 rows from the employees table, starting from the 11th row.

3. FETCH with ORDER BY

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 0
FETCH NEXT 5 ROWS ONLY;

This query retrieves the top 5 highest salaries from the employees table, sorted in descending order by salary, skipping 0 rows.

4. FETCH with DISTINCT

SELECT DISTINCT department_id
FROM employees
OFFSET 0
FETCH NEXT 3 ROWS ONLY;

This query retrieves the first 3 unique department_id values from the employees table, skipping 0 rows.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the FETCH clause to retrieve a specific number of rows after skipping a certain number of rows.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary NUMERIC(10, 2),
    department_id INT
);

In this example, we create a table named employees with columns for id, first_name, last_name, email, salary, and department_id.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('John', 'Doe', 'john.doe@example.com', 50000, 1);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Jane', 'Smith', 'jane.smith@example.com', 60000, 2);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Jim', 'Brown', 'jim.brown@example.com', 55000, 1);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Emily', 'Jones', 'emily.jones@example.com', 70000, 2);

Here, we insert data into the employees table.

Step 3: Using the FETCH Clause

This step involves using the FETCH clause to retrieve a specific number of rows from the employees table after skipping a certain number of rows.

These queries demonstrate how to use the FETCH clause to retrieve a specific number of rows from the employees table after skipping a certain number of rows, including using FETCH with OFFSET, ORDER BY, and DISTINCT.

-- Basic FETCH

SELECT first_name, last_name
FROM employees
OFFSET 0
FETCH NEXT 2 ROWS ONLY;

-- FETCH with OFFSET

SELECT first_name, last_name
FROM employees
OFFSET 1
FETCH NEXT 2 ROWS ONLY;

-- FETCH with ORDER BY

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 0
FETCH NEXT 2 ROWS ONLY;

-- FETCH with DISTINCT

SELECT DISTINCT department_id
FROM employees
OFFSET 0
FETCH NEXT 2 ROWS ONLY;

Conclusion

The PostgreSQL FETCH clause is a fundamental tool for limiting the number of rows returned by a query after a specified number of rows have been skipped. Understanding how to use the FETCH clause and its syntax is essential for effective data retrieval and pagination in PostgreSQL databases.