In PostgreSQL, temporary or temp tables are automatically removed at the end of a session/transaction, depending on their definition/creation. However, occasionally users may need to drop a temporary table before the current session or transaction expires. In such cases, the temporary tables can be dropped by using the DROP TABLE command. This post will illustrate a complete procedure for dropping a temporary table in PostgreSQL.

How to Explicitly Drop a TEMP Table in Postgres?

In Postgres, the TEMP or TEMPORARY keyword is used along with the “CREATE TABLE” command to create a temporary table. On the other hand, a temporary table can be explicitly dropped by executing a regular DROP TABLE command, without specifying a “TEMP or TEMPORARY” keyword. However, if the session/transaction has already terminated, then the temporary table will be automatically removed from the database, and there is no need to explicitly drop it.

To drop a temp table in Postgres, simply run the DROP TABLE command with the following syntax:

DROP TABLE IF EXISTS temporary_table_name;

Example: Explicitly Removing a TEMP Table in Postgres

Let’s first create a temporary table by executing the following command:

CREATE TEMP TABLE cp_table(
id INT,
name VARCHAR);

The above snippet shows that a temporary table has been created using a “TEMP” keyword. The table’s creation can be verified by running the below-provided command:

SELECT * FROM cp_table;

When it comes to removing a temporary table, there is no need of specifying the TEMP keyword. The following snippet demonstrates that a temporary table is removed just like a regular table:

DROP TABLE cp_table;

The table’s removal can be verified by executing the below-given query:

SELECT * FROM cp_table;

The output proves that the selected temporary table has been explicitly removed from the database.

Conclusion

In PostgreSQL, the temporary tables can be explicitly dropped/removed by executing the DROP TABLE command following the temporary table’s name. However, if the session/transaction has already terminated, then the temporary table will be automatically removed from the database, and there is no need to explicitly drop it. This article has illustrated a complete procedure of explicitly dropping a temporary table in PostgreSQL.