If you’ve been working with prepared statements in PostgreSQL, you may get to a point where you no longer need one or more of them. In that case, you might want to remove them, or delete them, drop them, or however you want to say it.

Fortunately, this is one of the quickest, easiest things to do in PostgreSQL.

The DEALLOCATE Command

PostgreSQL provides us with the DEALLOCATE command, which is used for removing prepared statements.

We can provide the name of a prepared statement to remove, or we can specify ALL to remove all prepared statements.

The syntax goes like this:

DEALLOCATE [ PREPARE ] { name | ALL }

So it’s pretty simple to use.

Remove All Prepared Statements

We can remove all prepared statements by using the ALL argument:

DEALLOCATE ALL;

That removes all prepared statements from the current session.

We also have the option of explicitly specifying PREPARE:

DEALLOCATE PREPARE ALL;

Remove a Specific Prepared Statement

To remove a specific prepared statement, provide the name of that prepared statement:

DEALLOCATE get_user_by_id;

In this case I removed a prepared statement called get_user_by_id.

Again, we can use the PREPARE keyword if we so wish:

DEALLOCATE PREPARE get_user_by_email;

Here, I removed the get_user_by_email prepared statement.

Non-Existent Prepared Statement

If we specify the name of a prepared statement that doesn’t exist, we get an error.

For example, here’s what happens when I run the above code again (after deleting the prepared statement):

DEALLOCATE PREPARE get_user_by_email;

Output:

ERROR:  prepared statement "get_user_by_email" does not exist

As expected, an error is returned.