Hello! Temporary tables are useful data storage unit while doing ETL tasks, and processing data. In this article i will show you how to create a temporary table in PostgreSQL?

Temporary Tables In PostgreSQL

Those tables are not stored in PostgreSQL permanently, when session ends, PostgreSQL automatically drops temporary tables defined during the session. That means if you need intermediate tables for your task, you can use temporary tables in PostgreSQL.

You can check official documentation about temporary tables to learn more about them.

When to use temporary tables?

When you need to store intermediate results of complex queries, you can use temporary tables. Those tables live during the session, so you can use those tables when you need a session long data.

Let’s assume you have a complex task like doing some mathematical calculations in a few steps. Temporary tables can help you to break complex task into manageable steps.

Creating temporary tables, helps to maintain database performance via not saving all unnecessary data permanently. For more details about monitoring performance you can refer to my previous article.

How to Create a Temporary Table in PostgreSQL?

Creating a temporary table is very similar to creating table. Let’s take one of our previous examples:

create table MYTABLE (mycolumn integer);

Statement above will create a regular table named MYTABLE. This table will exists forever on the database unless you drop that table. Let’s create a temporary table:

 create temp table mytable (mycolumn integer);

By default, Postgesql would create permanent tables in the schema of statement runner role/user. What about temporary tables? Let’s ask yo pg_tables view and find our where does it stores?

select * from pg_tables where tablename='mytable';

Table’s schema displayed as pg_temp_4, when creating a temporary table you can not set schema name. PostgreSQL will chose a schema to create create table.

If Temporary and permanent tables with same name exists on the table, temporary schemas are first on the search path, to access permanent table with same name, you need to mention schema name of the permanent table.

Autovacuum daemon can not access temporary tables. If you need to use temporary tables for complex queries with big amount of data, run ANALYZE commands in your session.

Lifetime of Temporary Tables

Right after creating temporary table, table is accessible.

Let’s finish session, reconnect and retry to access the table.

Temporary tables are dropped automatically when session ends.

Summary

We covered creating temporary tables in PostgreSQL topic. Also covered purpose of using temporary tables. Also demonstrated lifetime of temporary tables.

If you have a question please don’t hesitate to leave a comment below, or just drop a Hi! to meet. Looking forward to hear from you!

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch