8

I am inserting data into temp table and its working fine. below is the psedo sql code for the same

with cte as(

)
select * 
into temp_table
from cte

with this approach data is inserted very fast into temp table. As per my knowleged temp table is deleted once the session is closed. but my temp table is not deleted even if closed my pgadmin connection.

My question is does temp table in postgresql are deleted automatically or they remains on disk space until we delete them.

Regards,

Sanjay Salunkhe

Improve this question
2
  • Unrelated, but: the non-standard select into ... from ... is discouraged. Use create table ... as select ... to create a table from the result of a select statement. Commented Oct 26, 2017 at 13:24
  • will i tried this but somehow this approach is taking more time to insert query into table. don't know why?? Commented Oct 26, 2017 at 14:37

2 Answers 2

Reset to default
7

In fact you created a regular table. You have to specify that this is a temporary table:

with cte as(
-- <a_query>
)
select * 
into temporary temp_table
from cte;

or (the recommended syntax):

create temporary table temp_table as
-- <a_query>

See SELECT INTO and CREATE TABLE AS.

Improve this answer
3
  • klin: Does that mean that i am not creating temporary table here Commented Oct 26, 2017 at 14:29
  • Yes, you need a word temporary like in the answer. Commented Oct 26, 2017 at 14:33
  • CREATE TABLE AS vs SELECT INTO answer on Database Administrators Commented Oct 26, 2017 at 17:27
8

According to Postgres documentation temporary tables are dropped at end of a session or at end of a transaction.

TEMPORARY or TEMP

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

Improve this answer
1
  • 1
    do you have any idea when temp_schema are dropped? Commented Oct 23, 2019 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.