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:
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 useFIRST
paired withROW
to retrieve the first row of the results, orNEXT
paired withROWS
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