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:

In the above syntax, we have the following parameters:

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:

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:

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:

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.

Output

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