How to correctly use FETCH FIRST in Postgresql?

Question

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?

If you need example - its here. But if you need just a first row(s) from the query then using limit is the best way (in most cases). — Abelisto
– Abelisto, Commented Jul 19, 2016 at 20:21

jmelesky · Accepted Answer · 2016-07-19 20:26:49Z

59

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)

edited Jul 19, 2016 at 20:26

answered Jul 19, 2016 at 20:09

jmelesky

4,1101 gold badge26 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tyler Chong Over a year ago

And performance wise, those two are the same? They just do the same thing but different wording

jmelesky Over a year ago

Yep, identical. Purely a syntactic difference. I've posted the identical explain plans (for my simple query and small test table) above.

user1092126 Over a year ago

Try to perform test on table with multiple rows, I think Seq scan chosen by DB because of small records count

dpercy Over a year ago

Why do these two syntaxes exist?

jmelesky Over a year ago

One is the SQL standard, the other is a popular but nonstandard extension. The latter didn't originate with postgres, but they supported it eventually.

Collectives™ on Stack Overflow

How to correctly use FETCH FIRST in Postgresql?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related