PostgreSQL FETCH

Summary: in this tutorial, you will learn how to use the PostgreSQL FETCH clause to retrieve a subset of rows from a query.

Introduction to PostgreSQL FETCH clause #

In PostgreSQL, the OFFSETclause works like the LIMITclause. The FETCH clause allows you to limit the number of rows to return from a query.

The LIMIT clause is not a part of the SQL standard. However, the FETCH clause is a part of the SQL:2008 standard. If you want your application to support other databases in the future, use FETCH instead of LIMIT because other database vendors will likely support it.

Here’s the syntax of the OFFSET FETCH clause:

SELECT column1, column2
FROM table_name
ORDER BY sort_expression
OFFSET skip_count
FETCH { FIRST | NEXT } ] [row_count] {ROW | ROWS } ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the name of the table you want to retrieve data in the FROM clause.

Second, restrict which columns from the rows to include in the final result set in the SELECT clause.

Third, use the ORDER BY clause to sort the rows by values in one or more columns.

Fourth, provide the number of rows to skip in the OFFSET clause before the FETCH clause returns a subset of rows:

OFFSET skip_countCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The skip_count determines the number of rows to skip. It can be a zero or a positive integer. If skip_count is zero, the query will not skip any rows.

The OFFSET clause is optional. If you omit it, the query will also not skip any rows.

Fifth, set the number of rows to return (row_count) in the FETCH clause.

FETCH { FIRST | NEXT } ] [row_count] {ROW | ROWS } ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The row_count defaults to 1, meaning that if you omit it, the FETCH will return one row from the query.

You can use FIRST and NEXT, ROW, and ROWS interchangeably because they are synonyms.

For example:

FETCH FIRST 1 ROW ONLY;
FETCH FIRST ROW ONLY;
FETCH FIRST 10 ROWS ONLY;
FETCH NEXT 1 ROW ONLY;
FETCH NEXT ROW ONLY;
FETCH NEXT 10 ROWS ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Setting up a sample table #

We’ll use the products table created in the LIMIT tutorial to practice with the FETCH clause.

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2)
);

INSERT INTO
  products (name, price)
VALUES
  ('iPhone 16 Pro Max', 1649.00),
  ('iPhone 16', 829.00),
  ('Galaxy S24 Ultra', 1299.99),
  ('Galaxy S24 FE', 949.99),
  ('Pixel 9 Pro', 799.00),
  ('Pixel 8a', 399.00),
  ('OnePlus 12', 745.00),
  ('OnePlus Open', 1514.99),
  ('Galaxy Z Fold 6', 2019.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fetching the first row #

The following statement uses the FETCH clause to retrieve the most expensive product from the products table:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
FETCH FIRST ROW ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      name       |  price
-----------------+---------
 Galaxy Z Fold 6 | 2019.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fetching some rows #

The following example uses the FETCH clause to retrieve the top three most expensive products from the products table:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
FETCH FIRST 3 ROWS ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        |  price
-------------------+---------
 Galaxy Z Fold 6   | 2019.99
 iPhone 16 Pro Max | 1649.00
 OnePlus Open      | 1514.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Skipping some rows before fetching #

The following statement uses the OFFSET and FETCH clauses to get the second most expensive products from the products table:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
OFFSET 1
FETCH NEXT 1 ROW ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        |  price
-------------------+---------
 iPhone 16 Pro Max | 1649.00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using PostgreSQL FETCH clause for pagination #

The FETCH clause can be helpful for pagination:

OFFSET record_per_page * (page_no - 1)
FETCH NEXT record_per_page ROWS ONLY.Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• record_per_page is the number of records per page.
• page_no is the page number like 1, 2, and 3.

Suppose you want to display three products (record_per_page) per page and want to retrieve products for page 2 (page_no); you can use the following query:

SELECT
  name,
  price
FROM
  inventories
ORDER BY
  price DESC
OFFSET 3 * (2 - 1)
FETCH NEXT 3 ROW ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      name       | price
-----------------+--------
 iPhone 15 Black | 999.99
 Pixel 8 Pro     | 999.99
 Galaxy S23      | 899.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the PostgreSQL FETCH to return a subset of rows from a query.
• Use the OFFSET FETCH to skip some rows before returning a subset of rows.

Quiz #