HexaCluster Logo

Data Navigation with PostgreSQL Cursors – Part III

Welcome back to our series on PostgreSQL Cursors. As we continue exploring PostgreSQL Cursors, including Scrollable and Non-Scrollable Cursors, we’re now focusing on With-Hold Cursors in this article. With-Hold Cursors in PostgreSQL provide a crucial mechanism for managing data, enabling users to maintain query result sets even beyond transaction commitments.

Data Navigation with PostgreSQL Cursors.

What are With-Hold Cursors ?

With-Hold Cursors in PostgreSQL are cursor types that enable users to retain the query result set even after the transaction in which they were declared has been committed. Unlike other cursor types, which are automatically closed at the end of a transaction, With-Hold Cursors persist beyond transaction boundaries. This persistence allows for further processing or retrieval until explicitly closed or the session terminates. Consequently, users can maintain the cursor’s data for extended periods, facilitating iterative or interactive data manipulation tasks across transactions.

With-Hold Cursors

How to Use a With-Hold Cursor ?

Using a With-Hold Cursor is similar to using other types of cursors in PostgreSQL. To illustrate the practical use of With-Hold Cursors, let’s consider an example



  • cursor_name is the name you give to your cursor.
  • WITH HOLD tells PostgreSQL to keep the data even after you finish.
  • SELECT statement gets the data you want to work with.

Using a With-Hold Cursor

Imagine we have a list of employees in a table, and we want to work with their data using a With-Hold Cursor. Let us see how we can declare such a cursor and fetch data from the cursor.

-- Declare the With-Hold Cursor

-- Fetch data from the Cursor
FETCH NEXT FROM emp_cursor;

Note: We must close the WITH HOLD CURSOR because it won’t automatically close.

CLOSE emp_cursor;

Free Oracle to PostgreSQL Migration Assessment
Interested in migrating from Oracle or SQL Server to PostgreSQL?
Sign up for our Free Migration Assessment!

- No cost evaluation
- Smooth transition to PostgreSQL.
Please enable JavaScript in your browser to complete this form.
Type of Database Migration

With-Hold Cursors Across Transactions

Now, let’s explore how With-Hold Cursors maintain their accessibility across different transactions within the same session.

Let us consider a case where we have a Transaction A. We declare a With-Hold Cursor to fetch data from the employees table inside this transaction.

-- Transaction A

-- Declare a With-Hold Cursor

-- Fetch data from the Cursor
FETCH NEXT FROM emp_cursor;

-- Transaction A ends

Now, even after Transaction A has ended, we can still access the With-Hold Cursor from Transaction B, as long as it is from the same session as the Transaction A.

-- Transaction B

-- Fetch data from the With-Hold Cursor declared in Transaction A
FETCH NEXT FROM emp_cursor;

-- Transaction B ends

This demonstrates that With-Hold Cursors can be accessed within the same session, from any transaction, providing persistence across transaction boundaries.

Note: Please note that With-Hold Cursors in PostgreSQL cannot be accessed once the session is closed. It’s essential to ensure that you have completed all necessary operations with the cursor before ending your session to avoid losing access to the cursor data.


In conclusion, With-Hold Cursors offer significant advantages for data management in PostgreSQL. With-Hold Cursors are useful in PostgreSQL for keeping your data accessible throughout all transactions of a session. By understanding how to use them, you can make your work more efficient and flexible.

That wraps up our series on PostgreSQL Cursors. We hope you found these articles on Scrollable, Non-Scrollable and With-Hold cursors helpful.

Seeking specialized PostgreSQL support or Database Migrations assistance ? Get experts advice for effective data management, query tuning, database migrations and database optimization. Click here for personalized expertise to elevate your database performance and meet your unique business needs.

Stay tuned for more interesting topics:v:

Subscribe to our Newsletters and Stay tuned for more interesting topics.

 Contact Us Today!

Please enable JavaScript in your browser to complete this form.
Machine Learning Services
PostgreSQL Consulting Services
Migration Services


  • Jagadeesh Panuganti

    Jagadeesh Panuganti is currently working at HexaCluster as a Senior Developer. His areas of expertise include PostgreSQL and Oracle; he is particularly skilled in PostgreSQL development and has experience with challenging migrations from Oracle to PostgreSQL. He also has expertise using the MERN stack as a full-stack developer. Beyond database development, his skill set includes creating applications in C, Python and Java.

Add A Comment

We're glad you have chosen to leave a comment. Please keep in mind that all comments are moderated according to our privacy policy, and all links are nofollow. Do NOT use keywords in the name field. Let's have a personal and meaningful conversation.

Our PostgreSQL Services

Our Machine Learning Services

Share article

HexaCluster Logo

Get in Touch

© 2023 Company. All Rights Reserved. Privacy Policy.