YogeshChauhan . com

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"
amazon

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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

Array destructuring and Object destructuring in JavaScriptJavaScriptWhat are Class Constants in PHP?PHPSteps to Secure a VPNMiscHow to read Standard Input in Swift?SwiftDebugging in WordPress Part 3: SCRIPT_DEBUG and SAVEQUERIESWordPressLearn to create your skill bar using CSSCSS