How to correctly use FETCH FIRST in Postgresql?

by

The following statements are equivalent:

SELECT * FROM foo LIMIT 10;

and

SELECT * FROM foo FETCH FIRST 10 ROWS ONLY;

ROWS is interchangeable with ROW, which makes fetching just 1 a little more grammatically consistent.

FETCH FIRST X ROWS ONLY is part of the SQL standard, while, to my recollection, LIMIT is not. LIMIT is very popular, and much more terse, so it is also supported by postgres.

Edited to add: The two statements are only syntactically different. They generate exactly the same plans:

=# explain select * from foo fetch first 10 row only;
                         QUERY PLAN                          
-------------------------------------------------------------
 Limit  (cost=0.00..0.22 rows=10 width=68)
   ->  Seq Scan on foo  (cost=0.00..18.50 rows=850 width=68)

=# explain select * from foo limit 10;
                         QUERY PLAN                          
-------------------------------------------------------------
 Limit  (cost=0.00..0.22 rows=10 width=68)
   ->  Seq Scan on foo  (cost=0.00..18.50 rows=850 width=68)

Leave a Comment

tech