PostgreSQL supports a concept of prepared statements that help us optimize the server’s performance. For this purpose, the PREPARE statement is used. A prepared statement is automatically deallocated when the session terminates. However, occasionally users need to deallocate the prepared statement explicitly. In such cases, the DEALLOCATE command can be used.

This article illustrates how you can explicitly deallocate a prepared statement in Postgres.

How to Deallocate a Prepared Statement in PostgreSQL?

In PostgreSQL, if a prepared statement is no longer needed, it can be explicitly deallocated by executing the DEALLOCATE statement:

DEALLOCATE prepared_statement_name;

Let’s learn it practically.

Example: Deallocating a Prepared Statement in Postgres

Let’s execute the following command to get the list of prepared statements:

SELECT name, statement
FROM pg_prepared_statements;

In the above snippet, the “pg_prepared_statements” view is used to display a list of prepared statements along with their names and statement type:

Execute the following command to deallocate the “emp_info” statement explicitly:

DEALLOCATE emp_info;

The selected prepared statement has been explicitly deallocated:

To confirm the statement’s deallocation, execute the select query for the “pg_prepared_statements” view:

SELECT name, statement
FROM pg_prepared_statements;

From the above snippet, you can clearly see that the prepared statement named “emp_info” has been deallocated successfully.

Conclusion

A prepared statement is automatically deallocated when the session terminates. However, occasionally users need to deallocate the prepared statement explicitly. In such cases, use the DEALLOCATE command followed by the name of the prepared statement. This post has illustrated a detailed guide on deallocating a prepared statement in PostgreSQL.