2

What's the difference between FETCH FIRST and FETCH NEXT in PostgreSQL? The docs simply say

NEXT Fetch the next row

FIRST Fetch the first row of the query

which isn't much of an explanation. I created a sample table with sample rows, executed

SELECT * from users
FETCH FIRST 2 ROWS ONLY;
SELECT * from users
FETCH NEXT 2 ROWS ONLY;

and got exactly the same output. It's almost as if FETCH FIRST and FETCH NEXT are merely synonyms for LIMIT (which, I read, is not part of the SQL standard, unlike FETCH [FIRST|NEXT])

If the two are the same, why wouldn't the documentation say so? Or are there some differences after all?

This Stack Overflow answer implies they may function the same way in at least some RDBMSes, specifically in MS Server

1
  • 1
    Please clarify whether this is about SQL SELECT or about cursors. Nov 6, 2023 at 2:36

1 Answer 1

9

The FETCH command whose documentation page you linked is distinct from the FETCH FIRST and FETCH NEXT options in a SELECT statement. The latter are described within SELECT:

FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }
[...]
ROW and ROWS as well as FIRST and NEXT are noise words that don't influence the effects of these clauses

So indeed there's no difference between FIRST and NEXT in that context.

The behavior is the same as LIMIT, except that LIMIT cannot auto-extend the count to include all rows with the same rank as the last row, unlike FETCH with its WITH TIES option.


In the case of the FETCH command for cursors, the behavior of FETCH NEXT and FETCH FIRST are different. For instance this sequence of statements:

BEGIN;
DECLARE c SCROLL CURSOR  FOR select generate_series(1,5) AS n;
FETCH FIRST c;
FETCH NEXT c;
FETCH FIRST c;
CLOSE C;
COMMIT;

show that FETCH FIRST c goes back at the first row of results:

FETCH FIRST c;
 n 
---
 1
(1 row)

FETCH NEXT c;
 n 
---
 2
(1 row)

FETCH FIRST c;
 n 
---
 1
(1 row)
1
  • "LIMIT is not able to count identical rows as one row": lots of queries could be made much more efficient if it were
    – Quassnoi
    Nov 6, 2023 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.