PostgreSQL: Using the FETCH Clause for Partial Result Sets

Retrieve a specific subset of rows from a PostgreSQL query using the FETCH clause. This guide explains its usage for pagination and batch processing.

Using PostgreSQL's `FETCH` Clause for Partial Result Sets

PostgreSQL's `FETCH` clause lets you retrieve a subset of rows from a query's result set. This is useful for pagination, displaying a limited number of results, or processing data in batches. While PostgreSQL also supports the `LIMIT` clause, `FETCH` aligns more closely with standard SQL.

Understanding the `FETCH` Clause

The `FETCH` clause works with the `ORDER BY` clause to select a specific portion of the sorted result set. It's used to retrieve a limited number of rows from a query's result set. It's functionally similar to the `LIMIT` clause but adheres to standard SQL syntax.

`FETCH` Clause Syntax


OFFSET start_row FETCH { FIRST | NEXT } [ row_count ] ROWS ONLY;

OFFSET start_row: Skips `start_row` number of rows (default is 0).
FETCH FIRST | NEXT: Specifies whether to fetch the first set of rows or the next set.
[row_count]: Specifies the number of rows to fetch (default is 1).
ROWS ONLY: Ensures that only the specified number of rows are returned.

Examples Using the `FETCH` Clause

(Note: The examples below assume the existence of a table named 'Car' with columns `car_id`, `car_name`, and `car_model`. The "Test it Now" links are placeholders; to see these examples in action, you'd need to execute these queries on a PostgreSQL database. Screenshots of the output are present in the original text but cannot be included here. Please refer to the original document for visual confirmation.)

Example 1: Fetching the First Row

SELECT car_id, car_name, car_model
FROM Car
ORDER BY car_name
FETCH FIRST ROW ONLY;

    

Example 2: Fetching the First Six Rows

SELECT car_id, car_name
FROM Car
ORDER BY car_name
FETCH FIRST 6 ROWS ONLY;

    

Example 3: Fetching Subsequent Rows Using `OFFSET`

SELECT car_id, car_name, car_model
FROM Car
ORDER BY car_name
OFFSET 6 ROWS FETCH NEXT 4 ROWS ONLY;

    

Follow On

TutorialsArena