How to Perform a PostgreSQL FETCH Example

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
SELECT * FROM TABLE_NAME FETCH [ FIRST | NEXT ] number_of_rows [ ROW | ROWS ] ONLY;

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:

1
2
3
4
5
CREATE TABLE animals (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
TYPE VARCHAR NOT NULL
);

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO animals (name, TYPE)
VALUES ('Bear' ,'Mammal'),
    ('Owl' ,'Bird'),
    ('Turtle' ,'Reptile'),
    ('Elephant' ,'Mammal'),
    ('Crocodile' ,'Reptile'),
    ('Chameleons' ,'Reptile'),
    ('Salamander' ,'Amphibian'),
    ('Goose' ,'Bird'),
    ('Penguin' ,'Bird'),
    ('Dog' ,'Mammal'),
    ('Frog' ,'Amphibian'),
    ('Caecilians' ,'Amphibian'),
    ('Snake' ,'Reptile'),
    ('Leopard' ,'Mammal'),
    ('Flamingos', 'Bird');

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:

1
2
3
4
5
6
7
8
9
SELECT * FROM animals FETCH FIRST 5 ROWS ONLY;
 id |   name    |  TYPE
----+-----------+---------
  1 | Bear      | Mammal
  2 | Owl       | Bird
  3 | Turtle    | Reptile
  4 | Elephant  | Mammal
  5 | Crocodile | Reptile
(5 ROWS)

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:

1
2
3
4
5
6
7
8
9
SELECT * FROM animals OFFSET 4 ROWS FETCH FIRST 5 ROWS ONLY;
 id |    name    |   TYPE
----+------------+-----------
  5 | Crocodile  | Reptile
  6 | Chameleons | Reptile
  7 | Salamander | Amphibian
  8 | Goose      | Bird
  9 | Penguin    | Bird
(5 ROWS)

NOTE: The offset must not be negative.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * FROM animals FETCH FIRST 10 ROWS ONLY;
 id |    name    |   TYPE
----+------------+-----------
  1 | Bear       | Mammal
  2 | Owl        | Bird
  3 | Turtle     | Reptile
  4 | Elephant   | Mammal
  5 | Crocodile  | Reptile
  6 | Chameleons | Reptile
  7 | Salamander | Amphibian
  8 | Goose      | Bird
  9 | Penguin    | Bird
 10 | Dog        | Mammal
(10 ROWS)

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * FROM animals ORDER BY id DESC FETCH FIRST 10 ROWS ONLY;
 id |    name    |   TYPE
----+------------+-----------
 15 | Flamingos  | Bird
 14 | Leopard    | Mammal
 13 | Snake      | Reptile
 12 | Caecilians | Amphibian
 11 | Frog       | Amphibian
 10 | Dog        | Mammal
  9 | Penguin    | Bird
  8 | Goose      | Bird
  7 | Salamander | Amphibian
  6 | Chameleons | Reptile
(10 ROWS)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.