Menu
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?
931
If you want to select data from all the columns of the table, you can use an asterisk ( * ) shorthand instead of specifying all the column names. The select list may also contain expressions or literal values. Second, specify the name of the table from which you want to query data after the FROM keyword.
FETCH retrieves rows using a previously-created cursor. A cursor has an associated position, which is used by FETCH . The cursor position can be before the first row of the query result, on any particular row of the result, or after the last row of the result. When created, a cursor is positioned before the first row.
When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause as follows: SELECT last_name, first_name, city FROM contacts WHERE first_name = 'Joe' ORDER BY last_name DESC; This PostgreSQL ORDER BY example would return all records sorted by the last_name field in descending order.
The ORDER BY clause in PostgreSQL is used together with the SELECT statement to sort table data. The table data can either be sorted in ascending or descending order. By default, the data is sorted in ascending order.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
