A duplicate cursor error (SQLSTATE 42P03) occurs when attempting to declare a cursor with a name that already exists in the current session. Close the existing cursor before redeclaring it.
PostgreSQL maintains cursor names within a session scope. When you attempt to declare a cursor using DECLARE CURSOR or open a cursor in PL/pgSQL code, the database engine checks if a cursor with that name is already active. If it is, PostgreSQL raises error 42P03 to prevent ambiguity and conflicts. This error typically occurs in stored procedures or transactions when the same cursor name is reused without proper cleanup.
Use the CLOSE statement to explicitly close an open cursor. This is the most direct fix when you need to reuse a cursor name:
CLOSE cursor_name;
DECLARE cursor_name CURSOR FOR SELECT * FROM table_name;In PL/pgSQL functions:
IF NOT FOUND THEN
CLOSE my_cursor;
END IF;If you have multiple cursors in the same function or transaction, give each one a distinct name:
DECLARE
cursor_1 CURSOR FOR SELECT id FROM users;
cursor_2 CURSOR FOR SELECT id FROM orders;
BEGIN
OPEN cursor_1;
OPEN cursor_2;
-- Use both cursors
END;This avoids the duplicate name issue entirely.
When iterating with cursors in loops, always close the cursor before the next iteration if reusing the same name:
FOR i IN 1..10 LOOP
IF i > 1 THEN
CLOSE my_cursor;
END IF;
DECLARE my_cursor CURSOR FOR SELECT * FROM data WHERE id = i;
OPEN my_cursor;
-- Process cursor
END LOOP;Better approach: use a different cursor name for each iteration or use REFCURSOR variables.
Ensure cursors are closed even when errors occur:
BEGIN
DECLARE my_cursor CURSOR FOR SELECT * FROM users;
OPEN my_cursor;
-- Process cursor
EXCEPTION WHEN OTHERS THEN
IF FOUND THEN
CLOSE my_cursor;
END IF;
RAISE;
END;This pattern ensures cleanup happens regardless of whether the operation succeeds.
For more complex scenarios, consider using REFCURSOR variables:
CREATE OR REPLACE FUNCTION get_data()
RETURNS refcursor AS $$
DECLARE
curs refcursor;
BEGIN
OPEN curs FOR SELECT * FROM users;
RETURN curs;
END;
$$ LANGUAGE plpgsql;REFCURSOR provides better control and automatic cleanup at transaction boundaries.
PostgreSQL error code 42P03 falls under class 42 (Syntax Error or Access Rule Violation). Cursors are session-local unless created WITH HOLD, which allows them to persist across transaction boundaries. All cursors are implicitly closed at transaction end, so the duplicate cursor error typically occurs within a single transaction or stored procedure execution. In WITH HOLD cursors, the cursor persists until explicitly closed or the session ends. Understanding cursor scope and transaction boundaries is critical for avoiding this error in production code.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL