How to Perform a PostgreSQL FETCH Example

Introduction

When you want to limit the number of records that are returned from a PostgreSQL query, you would typically use the LIMIT clause in conjunction with your SELECT statement. However, the FETCH clause, which has been available in PostgreSQL since 2008, can also be used to retrieve a subset of rows from a table. The difference between LIMIT and FETCH is that FETCH makes use of a cursor, which allows you to read through a result set a few rows at a time. This tactic can be particularly valuable when a result set contains a large number of records. In this article, we’ll learn more about this clause and provide a PostgreSQL FETCH example to show how it can be used in a query.

Prerequisites

Before attempting to follow along with the PostgreSQL FETCH examples in this tutorial, be sure the following prerequisites are in place:

• PostgreSQL needs to be installed on your computer.

• You’ll need to have some basic knowledge of PostgreSQL.

PostgreSQL FETCH Syntax

Shown below is the basic syntax for the FETCH clause:

Let’s dig into the details of this syntax:

• First, we select the table from which we want to retrieve records using the SELECT statement.

• We then use the FETCH clause to specify the number of rows we want to return.

• The other options available with FETCH involve the cursor and its associated position. We can use FIRST paired with ROW to retrieve the first row of the results, or NEXT paired with ROWS to grab the next rows from wherever the cursor is currently positioned.

PostgreSQL FETCH Examples

Before we look at our first example, let’s create a table named animals. We can use this table in our examples:

Now, let’s add some records to this table:

Using the SELECT statement with FETCH clause

For our first example, we’ll use a SELECT statement to display a list of animals from our table. We will return just the first five rows from the table using the FETCH clause:

The FETCH offset

Using the OFFSET clause, we can specify where we want to start in the result set. Let’s see how it works in the following query:

NOTE: The offset must not be negative.

The next example will fetch just the first 10 rows from the table:

Let’s look at one more example. This time, we will fetch the las 10 rows from the table. To do this, we’ll use the ORDER BY clause as well as FETCH:

Conclusion

While it’s possible to use the LIMIT clause to return just a portion of the results from a query, the FETCH clause offers a bit more control and can work more efficiently when you’re dealing with large result sets. In this article, we discussed the FETCH clause and looked at some PostgreSQL FETCH examples to see how it’s used to limit the results of a query. With these examples to guide you, you’ll be able to implement FETCH in your own PostgreSQL queries.