postgresql
  1. postgresql-fetch
PreviousNext

FETCH - PostgreSQL Clause

The FETCH clause is used in PostgreSQL to control the subset of rows that are returned from a SELECT statement. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of the FETCH clause in PostgreSQL.

Syntax

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, ...]
[OFFSET {number | expression} ROWS]
[FETCH {FIRST | NEXT} {number | expression} ROWS ONLY]
  • OFFSET: Specifies the number of rows to skip before starting to return rows.
  • FETCH: Specifies the number of rows to return.

Example

Let's take a look at an example of using the FETCH clause in PostgreSQL.

SELECT *
FROM products
ORDER BY price DESC
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY;

In the above example, we:

  1. Selected all columns from the products table.
  2. Ordered the results by the price column in descending order.
  3. Skipped the first 5 rows.
  4. Returned the next 10 rows.

Explanation

The FETCH clause is used in combination with the OFFSET clause to skip a certain number of rows and retrieve a limited number of rows from a query result.

In the example above, we first ordered the rows in descending order by price, then skipped the first 5 rows using the OFFSET clause, and finally, used the FETCH clause to retrieve the next 10 rows.

Use

The FETCH clause is used to limit the number of rows returned by a SELECT statement. This is useful when querying large tables that contain millions of rows where you only want to see a small subset of the data.

Important Points

  • The FETCH clause is only available in PostgreSQL version 8.4 or higher.
  • The FETCH clause can only be used in combination with the OFFSET clause.
  • The OFFSET and FETCH clauses can significantly impact query performance, especially on large tables. Therefore, they should be used with caution.

Summary

The FETCH clause in PostgreSQL is used to limit the number of rows retrieved from a SELECT statement. In this tutorial, we discussed the syntax, example, output, explanation, use, and important points of the FETCH clause in PostgreSQL. With this knowledge, you can now use the FETCH clause to query large tables and retrieve a limited subset of data.

LimitCondition
Published on: