CODEWITHSUNDEEP
postgresql
Tyler Chong
Tyler Chong

Reputation: 710

How to correctly use FETCH FIRST in Postgresql?

I was trying to find out how to only select the first element of the SELECT query.

It seems alot of people use LIMIT 1 to only select the first one, but that doesn't seem like the best way to do it.

I was reading up on SELECT in the Postgresql docs, and it seems there is an option for a FETCH statement but I can't find any examples online, could someone explain to me how to correctly use it?

Upvotes: 34

Views: 37284

Answers (1)

jmelesky
jmelesky

Reputation: 3970

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)

Upvotes: 55

Related Questions