patternsqlMinor
Postgresql: when temporary tables are deleted in postgresql
Viewed 0 times
postgresqltablestemporaryaredeletedwhen
Problem
I am inserting data into temp table and its working fine. below is the psedo sql code for the same
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
with cte as(
)
select *
into temp_table
from ctewith 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
Solution
In fact you created a regular table. You have to specify that this is a temporary table:
or (the recommended syntax):
See SELECT INTO and CREATE TABLE AS.
with cte as(
--
)
select *
into temporary temp_table
from cte;or (the recommended syntax):
create temporary table temp_table as
-- See SELECT INTO and CREATE TABLE AS.
Code Snippets
with cte as(
-- <a_query>
)
select *
into temporary temp_table
from cte;create temporary table temp_table as
-- <a_query>Context
StackExchange Database Administrators Q#189389, answer score: 7
Revisions (0)
No revisions yet.