PostgreSQL - Cursor

In the area of database management, effective data retrieval is essential particularly when handling large datasets. PostgreSQL offers the functionality of a cursor which allows for incremental data retrieval from extensive result sets.

By using PostgreSQL cursor syntax, developers can manage memory more efficiently and enhance application performance while processing rows one at a time. In this article, We will learn about the Cursor in PostgreSQL by understanding various examples and so on.

What is a Cursor in PostgreSQL?

• A cursor in PostgreSQL is a database object that enables traversal over the result set of a query. It acts as a pointer that allows us to fetch rows sequentially.
• PostgreSQL cursors are particularly useful when working with large tables containing millions of records where traditional SELECT operations may lead to performance issues or even out-of-memory errors.

Syntax of Declaring a Cursor

DECLARE cursor_name CURSOR FOR query;

• DECLARE: The keyword used to declare a cursor.
• cursor_name: The name assigned to the cursor.
• query: The SQL query associated with the cursor

How to Fetch Data from a Cursor?

After declaring a cursor, we can get the data using FETCH. The FETCH gets the next row(s) from the cursor. If no row found, then it returns NULL.

FETCH [direction (rows)] FROM [cursor_name];

Parameters:

• '[direction (rows)]': Specifies the direction and number of rows to fetch. If no direction is specified, the default is NEXT.
• '[cursor_name]': The name of the cursor from which to fetch data.

Directions for FETCH

• NEXT: Fetches the next rows.
• PRIOR: Fetches the previous rows.
• FIRST: Fetches the first rows.
• LAST: Fetches the last rows.
• ABSOLUTE count: Fetches the row at the absolute position 'count'.
• RELATIVE count: Fetches the row at the relative position 'count'.
• ALL: Fetches all remaining rows.
• FORWARD count: Fetches the next 'count' rows.
• BACKWARD count: Fetches the previous 'count' rows.

Steps to Use Cursors in PostgreSQL

Let us take a look at an example of Cursor in PostgreSQL to better understand the concept.

Step 1: Create a Sample Table

Lets, create a sample table using the below commands for examples:

CREATE TABLE students (
 student_id serial PRIMARY KEY,
 full_name VARCHAR NOT NULL,
 branch_id INT
);
INSERT INTO students (
 student_id,
 full_name,
 branch_id
)
VALUES
 (1, 'M.S Dhoni', NULL),
 (2, 'Sachin Tendulkar', 1),
 (3, 'R. Sharma', 1),
 (4, 'S. Raina', 1),
 (5, 'B. Kumar', 1),
 (6, 'Y. Singh', 2),
 (7, 'Virender Sehwag ', 2),
 (8, 'Ajinkya Rahane', 2),
 (9, 'Shikhar Dhawan', 2),
 (10, 'Mohammed Shami', 3),
 (11, 'Shreyas Iyer', 3),
 (12, 'Mayank Agarwal', 3),
 (13, 'K. L. Rahul', 3),
 (14, 'Hardik Pandya', 4),
 (15, 'Dinesh Karthik', 4),
 (16, 'Jasprit Bumrah', 7),
 (17, 'Kuldeep Yadav', 7),
 (18, 'Yuzvendra Chahal', 8),
 (19, 'Rishabh Pant', 8),
 (20, 'Sanju Samson', 8)

Step 2: Declare and Use a Cursor

Now that the table is ready we can declare our cursor.

Query:

BEGIN;
DECLARE 
 my_cursor CURSOR FOR SELECT * FROM students;

Fetch the data.

FETCH 10 FROM my_cursor;

Output:

FETCH PRIOR FROM my_cursor;
FETCH PRIOR FROM my_cursor;

The above query will give you row 9 and 8 since right now our cursor is at 10;

FETCH 6 FROM my_cursor;

Output:

Step 3: Commit the Transaction

Commit the transaction at the end.

COMMIT;

Important Points About Cursor in PostgreSQL

• Cursors must be declared inside a transaction block (BEGIN … COMMIT) unless you rely on session-level scope.
• Cursors are not guaranteed to be scrollable by default; use SCROLL to allow backward movement or NO SCROLL to make them forward-only.
• The default cursor type is INSENSITIVE, meaning it does not reflect table changes made after the cursor is opened.
• PostgreSQL also supports BINARY cursors, which return results in binary format for faster processing.
• Fetching rows in controlled chunks (e.g., FETCH 100) improves performance and reduces memory usage when working with large result sets.