Yogesh Chauhan's Blog

FETCH clause in PostgreSQL

in Postgres on September 8, 2020

FETCH – retrieve a portion of rows

We learned about LIMIT clause in this post: LIMIT And OFFSET In Postgres

To limit the number of rows returned by a query, we use the LIMIT clause. Similarly, we can use FETCH clause to do the same.

Syntax:


OFFSET start_point { ROW or ROWS }
FETCH { FIRST or NEXT } [number_of_rows ] { ROW | ROWS } ONLY

The start_point must be a zero or positive integer number.

As we saw in the LIMIT blog post, if the OFFSET clause is not specified then Postgres will consider it as 0. If you provide the start_point higher than the number of rows in result set then you will get zero rows in return, which makes sense.

Value of number_of_rows is one by default. You can set it as 1 or higher values.

When using FETCH, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query’s rows.

Examples:

I am using this database available on my public repo on Github

FETCH FIRST ROW ONLY without ORDER BY:


SELECT customer_id, contact_name 
FROM customers 
FETCH FIRST ROW ONLY;

//Output
customer_id, contact_name
"ALFKI"    "Maria Anders"

FETCH FIRST ROW ONLY with ORDER BY:


SELECT customer_id, contact_name 
FROM customers 
ORDER BY customer_id 
FETCH FIRST ROW ONLY;

//Output
customer_id, contact_name
"ALFKI"    "Maria Anders"

Now, if you use FIRST ROW or FIRST 1 ROW, both will give same results.

Let’s use the recent example with FIRST 1 ROW:

FETCH FIRST 1 ROW ONLY with ORDER BY:


SELECT customer_id, contact_name 
FROM customers 
ORDER BY customer_id 
FETCH FIRST 1 ROW ONLY;

//Output
customer_id, contact_name
"ALFKI"    "Maria Anders"

Of course, if we want more than 1 row then it is useful. 

FETCH FIRST 3 ROWS ONLY with ORDER BY:


SELECT customer_id, contact_name 
FROM customers 
ORDER BY customer_id 
FETCH FIRST 3 ROW ONLY;

//Output
customer_id, contact_name
"ALFKI"	"Maria Anders"
"ANATR"	"Ana Trujillo"
"ANTON"	"Antonio Moreno"

If you want to skip first row, there is a way, Use OFFSET.

FETCH FIRST 3 ROWS ONLY with ORDER BY but skip the FIRST ROW with OFFSET:


SELECT customer_id, contact_name 
FROM customers 
ORDER BY customer_id OFFSET 1
FETCH FIRST 3 ROW ONLY;

//Output
customer_id, contact_name 
"ANATR"	"Ana Trujillo"
"ANTON"	"Antonio Moreno"
"AROUT"	"Thomas Hardy"

As we can see the first row has been removed from results. I’ve used FIRST rather than NEXT. Both of them are fine. You can use any word. Also, if you use ROW or ROWS, both are fine.

FETCH FIRST 3 ROWS ONLY with ORDER BY but skip the FIRST ROW with OFFSET (using NEXT):


SELECT customer_id, contact_name 
FROM customers 
ORDER BY customer_id OFFSET 1
FETCH NEXT 3 ROW ONLY;

//Output
customer_id, contact_name 
"ANATR"	"Ana Trujillo"
"ANTON"	"Antonio Moreno"
"AROUT"	"Thomas Hardy"

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How can one check to see if a remote file exists using PHP?PHPSlider animation using CSS transform propertyCSSModules and its Core features in JavaScriptJavaScriptJavaScript Number MethodsJavaScriptSelector Lists and Combinators in SCSS (Sass)SCSS@use rule in SCSS (Sass)SCSS