Fetch in PostgreSQL

Introduction

When you query a table in PostgreSQL, there may be certain situations where you only want to retrieve a specific subset of the rows in the result set. In these cases, the FETCH command can be used to retrieve a certain number of rows returned by a query. With this command, you can specify which direction to fetch; for example, you may want the first X number of rows or the last X number of rows in the result set. You can also specify the number of rows to return. In this article, we’ll provide several helpful examples of using FETCH in PostgreSQL.

Prerequisite

Let’s begin this tutorial by reviewing the two key prerequisites that are necessary for the task:

  • You’ll need to make sure that PostgreSQL is installed and properly configured on your system.
  • You’l need a basic understanding of SQL in order to follow along with the examples in this article.

PostgreSQL Sample Document

Our first step will be to create a PostgreSQL sample document that we can use in our examples. You’ll need to perform the following steps:

  1. First, log in to your PostgreSQL shell.
  2. Create a database named mobiledb.
  3. Then, connect to the database using the psql command \c.
  4. Finally, create a table named mobile and insert documents at the same time.

Here are the SQL statements needed to accomplish those steps:

CREATE DATABASE mobiledb
postgres=# \c  mobiledb
You are now connected TO DATABASE "mobiledb" AS USER "postgres".
mobiledb=# CREATE TABLE mobile(
mobiledb(#    id VARCHAR (100),
mobiledb(#    brand VARCHAR (100),
mobiledb(#    model VARCHAR (100)
mobiledb(# );
mobiledb(# );
CREATE TABLE

Now that we’ve created our database and table, we can go ahead and insert some sample records:

 INSERT INTO mobile(id,brand,model) VALUES
mobiledb-#  ('1001','Samsung','Galaxy Note10 Lite'),('1002','Samsung','Galaxy S10 Lite'),('1003','Samsung','Galaxy A10'),('1004','Samsung','Galaxy A71'),
mobiledb-# ('1005','Apple','Iphone 6'),('1006','Apple','Iphone 5s'),('1007','Apple','Iphone 6'),('1008','Apple','Iphonex');
INSERT 0 8

We receive a response stating: INSERT 0 8.

We can verify that our insert operation was successful using the SELECT * command:

mobiledb=# SELECT * FROM mobile;
  id  |  brand  |       model
------+---------+--------------------
 1001 | Samsung | Galaxy Note10 Lite
 1002 | Samsung | Galaxy S10 Lite
 1003 | Samsung | Galaxy A10
 1004 | Samsung | Galaxy A71
 1005 | Apple   | Iphone 6
 1006 | Apple   | Iphone 5s
 1007 | Apple   | Iphone 6
 1008 | Apple   | Iphonex
(8 ROWS)

PostgreSQL Fetch Example

At this point, we have our sample dataset ready, so we can try performing a FETCH in PostgreSQL.

SELECT
    brand,
    model
FROM
    mobile
ORDER BY
    brand
FETCH FIRST ROW ONLY;

The SQL query shown above will select both brand and model fields from the mobile table and will sort the results by the brand field. Notice that we are “fetching” the first row only from the result set.

Now let’s try a slightly different SQL query:

SELECT
    brand,
    model
FROM
    mobile
ORDER BY
    brand
FETCH FIRST 5 ROW ONLY;

The above SQL query will select the same columns from the same table as the previous example. The difference is that we specify the number of rows to be returned. In this case, we’re returning five rows that will be sorted by the field brand.

The results should look something like this:

  brand  |       model
---------+--------------------
 Apple   | Iphonex
 Apple   | Iphone 6
 Apple   | Iphone 5s
 Apple   | Iphone 6
 Samsung | Galaxy Note10 Lite
(5 rows)

We can also use a cursor with fetch for a shorter query that’s easier to read.

NOTE: The DECLARE CURSOR statement can only be used in transaction blocks.

To do this, we’ll perform the following commands:

BEGIN WORK;

The output of this statement is:

BEGIN

Next, we’ll proceed with the DECLARE option:

DECLARE phones SCROLL CURSOR FOR SELECT * FROM mobile;

The output would be:

DECLARE CURSOR

After that, we use the FETCH command as shown below:

FETCH FORWARD 5 FROM phones;

In this code, the cursor position is at the front of the result set; therefore, moving forward and selecting the results that are five rows from phone will give us a result set like the following:

mobiledb=# FETCH FORWARD 5 FROM phones;
  id  |  brand  |       model
------+---------+--------------------
 1001 | Samsung | Galaxy Note10 Lite
 1002 | Samsung | Galaxy S10 Lite
 1003 | Samsung | Galaxy A10
 1004 | Samsung | Galaxy A71
 1005 | Apple   | Iphone 6
(5 ROWS)

Finally, we’ll perform the CLOSE and COMMIT to complete the transaction cycle.

CLOSE PHONES;
COMMIT WORK;

Conclusion

When you only need a certain subset of a query’s result set, the FETCH command can help you target the exact information you need. In this article, we showed how you can use FETCH in PostgreSQL to fetch just the first document in a result set or a specified number of documents from the results. We also explained how to use FETCH with a cursor to gain even more control over your query results. With these examples to use as a guide, you’ll be able to incorporate the FETCH command into your own SQL 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.