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;
Explanation:
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 within a transaction block using
BEGIN and COMMIT. Outside of a transaction, the cursor's scope is limited to the session.
- By default, cursors in PostgreSQL are scrollable, meaning you can fetch rows in any direction (NEXT, PRIOR, FIRST, LAST). Non-scrollable cursors can only move forward and are declared with '
NO SCROLL'.
- PostgreSQL supports different types of cursors like
BINARY and INSENSITIVE. The default cursor is INSENSITIVE, meaning it does not reflect changes made after the cursor is opened.
- Fetching data in smaller chunks (e.g., '
FETCH 100 FROM my_cursor') can improve performance by reducing memory consumption and processing time.
Conclusion
Overall, utilizing cursors in PostgreSQL can improve the efficiency of your database interactions. By mastering how to declare a cursor in PostgreSQL and fetch data from a PostgreSQL cursor, you can streamline operations on large tables without compromising system resources. Adopting cursors will enable you to optimize performance and provide a smoother data handling experience in your applications.
Similar Reads
PostgreSQL - INSERT
PostgreSQL INSERT statement is one of the fundamental SQL commands used to add new rows to a specified table within a PostgreSQL database. This command allows users to insert data efficiently, whether for a single record or multiple records at once. With the PostgreSQL INSERT INTO clause, we can spe
4 min read
PostgreSQL - COMMIT
The COMMIT command in PostgreSQL is important for saving the changes made during a transaction. Without executing a COMMIT, all the data manipulation operations performed within the transaction will be lost once the session ends. It ensures that the changes made to the database are permanent and vis
4 min read
PostgreSQL - Alias
PostgreSQL aliases are powerful tools that allow you to assign temporary names to tables or columns within your queries. These aliases only exist during the execution of the query, making your SQL code more readable and efficient.What is a PostgreSQL Alias?An alias in PostgreSQL is a temporary name
2 min read
PostgreSQL FETCH Clause
The PostgreSQL FETCH clause is an essential feature for controlling and managing the number of rows returned in our SQL queries. It provides a standardized approach for limiting results, similar to the LIMIT clause but with more flexibility and compatibility across different database systems. This a
4 min read
PostgreSQL - For Loops
In PostgreSQL, PL/pgSQL (Procedural Language/PostgreSQL) introduces control structures like FOR loops to simple complex data processing. The FOR loop allows developers to iterate over a specified range of integers or the results of a query and making repetitive tasks more manageable. This feature is
6 min read
PostgreSQL - ADD COLUMN
In PostgreSQL, the ADD COLUMN statement is a powerful command used to modify an existing database table by adding one or more new columns. This feature is important for adapting table structures to meet evolving data requirements, and it plays a key role in database management and optimization.In th
4 min read
MySQL Cursors
A MySQL cursor is a powerful database object designed for retrieving, processing, and managing rows from a result set one at a time. Unlike standard SQL queries that handle sets of rows in bulk, cursors allow for detailed row-by-row operations. In this article, We will learn about MySQL Cursors in d
6 min read
PostgreSQL - BEGIN
The BEGIN command in PostgreSQL is essential for transaction management, allowing a sequence of SQL operations to be executed as a single unit of work. This ensures data consistency and reliability by grouping operations together and enabling us to commit or rollback the entire set of changes.This a
4 min read
PostgreSQL - CREATE INDEX
The PostgreSQL CREATE INDEX statement is essential for improving database performance, allowing faster data retrieval by creating indexes on specified columns. Indexes in PostgreSQL act like pointers, significantly reducing the time required for query processing, especially on large tables. In this
5 min read
PostgreSQL - Psql commands
PostgreSQL, or Postgres, is an object-relational database management system that utilizes the SQL language. PSQL is a powerful interactive terminal for working with the PostgreSQL database. It enables users to execute queries efficiently and manage databases effectively.Here, we highlight some of th
2 min read