PostgreSQL Fetch Clause

In this section, we are going to understand the working of the PostgreSQL FETCH clause, which is used to repond a portion of rows returned by a particular statement.

The various RDBMS (relational database management systems) like H2, MySQL, and HSQLDB use the LIMIT clause extensively.

And we have learnt Limit Clause in an earlier section of the PostgreSQL tutorial that it is used to constrain the number of rows returned by a statement. And the LIMIT clause is not following a SQL-standard.

Therefore, to follow the SQL standard, the PostgreSQL also introduces the FETCH clause, which is used to recover various rows returned by a command. And the FETCH clause was launched in 2008 by SQL.

Syntax of the PostgreSQL Fetch Clause

The general syntax of the PostgreSQL FETCH clause is as follows:

OFFSET start { ROW | ROWS }  

FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

In the above syntax, we have the following parameters:

Parameter	Description
Start	It is an integer whose essential value is Positive or Zero; otherwise takes its default value, which is zero.
ROW | ROWS	Here, the Row is the replacement for ROWS, which means the ROW is the alternative for other ROWS; that’s why we can use them equivalently.
Row_count	If we do not define the row_count clearly, it will take its default value, which is one.

Note:

• If the OFFSET clause is not defined, then the start is larger than the number of rows in the outcome, and no rows are returned as the order of rows kept in that table is unnamed.
• If we want to make our software compatible with other database systems, we should use the FETCH clause as it follows the standard SQL, and the FETCH clause is functionally comparable to the LIMIT clause.

Examples of PostgreSQL FETCH Clause

To understand the PostgreSQL fetch clause working in real-time, we are going to use the CAR table, which we created in the Limit clause section of the PostgreSQL tutorial.

Here we select the Car_id, Car_name, and Car_model columns from the Car table, and fetch the first row which is sorted by Car_ name in the ascending order with the help of the FETCH clause as we can see in the following command:

SELECT car_id, car_name, car_model  

FROM Car  

ORDER BY car_name   

FETCH FIRST ROW ONLY;

Output

After executing the above command, we will get the below output, which displays only the first row from the CAR table.

Or we can use the below command as both the queries generate the same output:

SELECT car_id, car_name, car_model  

FROM Car  

ORDER BY car_name   

FETCH first 1 ROW ONLY;

Output

After executing the following command, we will get the below output, which displays a similar output compared to the above command:

We can use the below command if we want to select the first six row, which is sorted by their Car_name:

SELECT car_id, car_name  

FROM Car  

ORDER BY car_name   

FETCH first 6 ROW ONLY;

Output

After executing the above command, we will get the below output, which displays the first -six row, which is sorted by their Car_names from the CAR table.

In the below example, we will try to get the next four Car, which came after the first six-car that is sorted by Car_name from the CAR table.

SELECT car_id, car_name, car_model  

FROM Car  

ORDER BY car_name  

OFFSET 4 ROWS   

FETCH first 4 ROW ONLY;

Output

After executing the above command, we will get the below output, which displays the following four Cars details: