PostgreSQL FETCH Clause

The FETCH clause retrieves a specified number of rows from a query result, optionally skipping rows first with OFFSET. It is the SQL:2008 standard alternative to LIMIT and is preferred when writing SQL that must be portable across different database systems such as Oracle, DB2, and SQL Server.

Syntax

SELECT column_list
FROM table_name
ORDER BY sort_expression
OFFSET rows_to_skip ROWS
FETCH FIRST row_count ROWS ONLY;

Key syntax notes:

• FETCH FIRST and FETCH NEXT are synonyms — both are valid.
• ROW and ROWS are synonyms — FETCH FIRST 1 ROW ONLY and FETCH FIRST 1 ROWS ONLY are identical.
• row_count defaults to 1 when omitted: FETCH FIRST ROW ONLY returns exactly one row.
• In SQL:2008, OFFSET must come before FETCH, but PostgreSQL also accepts them in the reverse order.
• Always include ORDER BY — without it, the rows returned are non-deterministic.

Practical Example

Create a sample table for employee records:

CREATE TABLE employees (
  emp_id      SERIAL PRIMARY KEY,
  full_name   VARCHAR(100) NOT NULL,
  department  VARCHAR(50)  NOT NULL,
  salary_usd  NUMERIC(10,2) NOT NULL,
  hire_date   DATE          NOT NULL
);

INSERT INTO employees (full_name, department, salary_usd, hire_date) VALUES
  ('Aiko Tanaka',     'Engineering', 98000.00, '2021-03-15'),
  ('Bruno Ferreira',  'Marketing',   72000.00, '2020-07-01'),
  ('Clara Stein',     'Engineering', 115000.00,'2019-11-20'),
  ('David Osei',      'Operations',  65000.00, '2022-01-10'),
  ('Elena Novak',     'Engineering', 105000.00,'2020-04-05'),
  ('Fatima Al-Amin',  'Marketing',   78000.00, '2021-09-14'),
  ('Giorgio Russo',   'Operations',  61000.00, '2023-02-28'),
  ('Hana Kobayashi',  'Engineering', 122000.00,'2018-06-01');

Fetch the single highest-paid employee:

SELECT emp_id, full_name, salary_usd
FROM employees
ORDER BY salary_usd DESC
FETCH FIRST ROW ONLY;

Expected result:

 emp_id |  full_name      | salary_usd
--------+-----------------+------------
      8 | Hana Kobayashi  |  122000.00

Fetch the top 3 earliest hires:

SELECT emp_id, full_name, hire_date
FROM employees
ORDER BY hire_date
FETCH FIRST 3 ROWS ONLY;

Page through the employee list in batches of 3 — page 2 (rows 4–6 by salary):

SELECT emp_id, full_name, salary_usd
FROM employees
ORDER BY salary_usd DESC
OFFSET 3 ROWS
FETCH FIRST 3 ROWS ONLY;

Verify the pagination math:

SELECT COUNT(*) AS total_employees FROM employees;
-- 8 rows total: 3 rows per page = 3 pages (page 3 returns 2 rows)

FETCH vs. LIMIT

FETCH and LIMIT are functionally equivalent in PostgreSQL. The choice between them is a matter of portability and style:

-- These two queries are identical in PostgreSQL:
SELECT emp_id, full_name FROM employees ORDER BY salary_usd DESC LIMIT 5;
SELECT emp_id, full_name FROM employees ORDER BY salary_usd DESC FETCH FIRST 5 ROWS ONLY;

Keyset Pagination Alternative

For large tables, replace OFFSET with a keyset filter to avoid scanning all preceding rows:

-- Page 1
SELECT emp_id, full_name, salary_usd
FROM employees
ORDER BY salary_usd DESC, emp_id DESC
FETCH FIRST 3 ROWS ONLY;

-- Page 2 — use last salary and emp_id from previous result
SELECT emp_id, full_name, salary_usd
FROM employees
WHERE (salary_usd, emp_id) < (105000.00, 5)
ORDER BY salary_usd DESC, emp_id DESC
FETCH FIRST 3 ROWS ONLY;

The tie-breaking on emp_id ensures stable pagination when multiple employees share the same salary.

Testing with Vela

Testing pagination changes — especially a migration from OFFSET-based to keyset pagination — requires production-scale data to expose performance differences. Vela’s database branching lets you clone your production database instantly and run benchmarks against real data volumes on a branch before deploying the change. This ensures your new pagination logic returns the correct pages and performs as expected under realistic load.

Production Tips

• Always pair FETCH with ORDER BY — without it, the returned rows are arbitrary and may differ between executions.
• An index on the ORDER BY column lets PostgreSQL use an efficient index scan and stop after collecting the requested rows.
• FETCH FIRST 1 ROW ONLY is a readable guard to assert that a subquery returns at most one row.
• For pagination beyond the first few hundred pages, switch to keyset pagination to avoid the linear O(offset) scan cost.
• Use EXPLAIN ANALYZE to verify the planner generates a Limit node over an index scan rather than a full sequential scan followed by a sort.
• In migration scripts that must run on multiple database engines, prefer FETCH for portability.