PostgreSQL Duplicate Prepared Statement (SQLSTATE 42P05)

PostgreSQL raises ERROR: prepared statement "<name>" already exists with SQLSTATE 42P05 and condition name duplicate_prepared_statement when a PREPARE command attempts to create a named prepared statement using a name that is already in use within the current session.

What This Error Means

SQLSTATE 42P05 belongs to PostgreSQL error class 42 — Syntax Error or Access Rule Violation. This class covers a broad range of errors where a statement is syntactically valid but violates a rule about how objects can be used, including naming conflicts with session-scoped objects.

Prepared statements in PostgreSQL are session-local: they exist from the moment PREPARE is called until either DEALLOCATE is called, the session ends, or the statement is replaced via a protocol-level mechanism. Unlike temporary tables, there is no CREATE OR REPLACE equivalent for PREPARE — you must explicitly deallocate the old statement before reusing its name.

When 42P05 is raised, the new PREPARE command has failed and the existing prepared statement with that name is left completely intact. The error does not affect any open transaction beyond the statement that failed; the session remains usable and the existing prepared statement can still be executed or deallocated.

Common Causes

1. Reusing a statement name without deallocating first. Application code calls PREPARE my_stmt AS ... on every request or at startup without checking whether the statement already exists from a previous call in the same long-lived connection.

2. Connection pool warm-up running multiple times on the same connection. A connection pool initializer or ORM bootstrap routine prepares statements when a connection is first checked out, but the connection is later returned to the pool and re-initialized, issuing the same PREPARE commands again.

3. Retry logic that replays the full statement block. When a transaction fails and the application retries from scratch, it may replay a PREPARE that already succeeded (and was not rolled back, because PREPARE outside a transaction block is not transactional in the session-name sense).

4. Explicit SQL PREPARE in application code alongside driver-level prepared statements. Some drivers use named prepared statements internally. If application code also issues PREPARE with a name that clashes with a driver-generated name, the conflict can be hard to trace.

How to Fix duplicate_prepared_statement

1. Deallocate before re-preparing. The simplest and most reliable fix is to DEALLOCATE the old statement before issuing PREPARE again:

   DEALLOCATE my_stmt;
   PREPARE my_stmt AS SELECT * FROM orders WHERE id = $1;
   

2. Use DEALLOCATE ALL during connection setup. If your application prepares a fixed set of statements at connection startup, clear all existing prepared statements first to avoid conflicts on reuse:

   DEALLOCATE ALL;
   PREPARE get_order AS SELECT * FROM orders WHERE id = $1;
   PREPARE list_orders AS SELECT * FROM orders WHERE customer_id = $1;
   

3. Check for existence before preparing. Query pg_prepared_statements to guard the PREPARE call:

   DO $$
   BEGIN
     IF NOT EXISTS (
       SELECT 1 FROM pg_prepared_statements WHERE name = 'my_stmt'
     ) THEN
       EXECUTE 'PREPARE my_stmt AS SELECT * FROM orders WHERE id = $1';
     END IF;
   END;
   $$;
   

   Note that PREPARE cannot appear directly inside a DO block, so EXECUTE is required there.

4. Rely on extended query protocol instead of SQL-level PREPARE. Most modern PostgreSQL drivers (libpq, JDBC, psycopg, node-postgres) use the wire-protocol extended query flow for parameterized queries, which manages statement names internally and avoids conflicts with application-level SQL PREPARE. If you are writing explicit PREPARE statements in application code, consider switching to parameterized queries via the driver API instead.

5. Use unique statement names per request. For cases where you genuinely need SQL-level prepared statements and cannot guarantee cleanup, include a session-unique suffix in the name (e.g., a sequence number or UUID). This trades the naming conflict for eventual accumulation, so pair it with periodic DEALLOCATE ALL or explicit cleanup.

Additional Information

• pg_prepared_statements is a system view that lists all currently prepared statements in the session. Querying it is a reliable way to inspect or guard against duplicates.
• Related SQLSTATE codes in the same 42 class include 42P06 (duplicate_schema), 42P07 (duplicate_table), and 42710 (duplicate_object) — all follow the same pattern of naming conflicts for session or database objects.
• PostgreSQL does not support CREATE OR REPLACE semantics for PREPARE. The only way to update a prepared statement is to DEALLOCATE and then PREPARE again.
• In PL/pgSQL, the EXECUTE statement for dynamic SQL does not use session-level prepared statement names, so 42P05 is not triggered from within stored functions using EXECUTE.
• Connection poolers such as PgBouncer in transaction pooling mode reset prepared statements between transactions (or can be configured to do so), which can cause applications that rely on session-level prepared statements to see errors. In that mode, explicit SQL PREPARE/EXECUTE is not supported and drivers must use protocol-level statements only.

Frequently Asked Questions

Why doesn't PREPARE just replace the existing statement like CREATE OR REPLACE? PostgreSQL treats prepared statement names as session-scoped identifiers without an atomic replace operation. This is intentional: a concurrent EXECUTE of the old statement could be in flight, and silently replacing it would produce unpredictable results. The explicit DEALLOCATE + PREPARE sequence makes the replacement visible and deliberate.

Does rolling back a transaction undo a PREPARE? No. PREPARE creates a session-level object, not a transactional one. If you issue PREPARE inside a transaction and then ROLLBACK, the prepared statement still exists. This surprises developers who expect full rollback of everything inside a transaction block.

How can I list all prepared statements in my current session? Query the pg_prepared_statements view:

SELECT name, statement, prepare_time, parameter_types
FROM pg_prepared_statements;

Will this error occur when using an ORM like SQLAlchemy or Hibernate? Usually not directly, because most ORMs use the driver's extended query protocol rather than explicit SQL PREPARE statements. If you see 42P05 through an ORM it typically means the application is issuing raw SQL that includes PREPARE, or the ORM has a bug in its connection re-initialization logic.