Cannot create temporary tables in postgres using psycopg2

Question

I am trying to create a temporary table in postgres. I am using psycopg2. However upon executing my python script i do not see the table being created. If i try to create a normal table, it executes without any problem. Is there any concept that i missing?My intention is to create a temp table and then import data into the temp table, do some processing and then push the temp table data into a permanent table.

Below is my code

import psycopg2

con = None
con = psycopg2.connect("dbname='abc' user='abc' host='localhost' password='abc'")
cur = con.cursor()
cur.execute("create TEMP TABLE mytable(id int);")
con.commit()
con.close()

If i execute the same create TEMP TABLE mytable(id int); using the terminal, it creates the temp table.

Thank you @Thom Brown for your reply.

I did think of your point. To bypass the issue of con.close(), i did the following, but NO success.

con = None
con = psycopg2.connect("dbname='abc' user='abc' host='localhost' password='abc'")
cur = con.cursor()
cur.execute("create temp table mynewtable(id int) on commit delete rows;")
cur.execute("BEGIN TRANSACTION;")
cur.execute("insert into mynewtable values(10);")
//created a new normal table from console
cur.execute("select * into dbo.test from mynewtable;")
#cur.execute("select * into test from mynewtable;")
cur.execute("commit")
con.commit()
con.close()

Theoretically, i should be able to create the temp table using the execute command, insert some data into it, transfer all the data from the temp table to a permanent table, close the connection. The temp table datawill be lost but the permanent table should have the transferred data. This is not happening. Please guide.

Thom Brown · Accepted Answer · 2020-05-15 09:29:47Z

4

Temp tables are destroyed when the sessions ends. When you close the connection using con.close() temp tables will be dropped.

If you wish to use the temp table, you will need to do it prior to closing the connection.

answered May 15, 2020 at 9:29

Thom Brown

2,0698 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Thom Brown Over a year ago

With your example, do you have a table named 'test', but with no data in it?

codeheadache Over a year ago

Yes, i have created a separate normal table directly using the terminal.

Thom Brown Over a year ago

You're using SELECT INTO, meaning you don't need to manually create the table. That statement will fail if you're creating it elsewhere.

codeheadache Over a year ago

Thats awesome!! Using select into to create the permanent table did the trick.However this means, i cannot create my own permanent table from the temp table, say for example with extra columns. I have to issue another query to add/alter the permanent table

Thom Brown Over a year ago

If you want to insert the data into a table you've created ahead of time, you just need to use something like INSERT INTO test (col1,col2,col3) SELECT col1, col2, col3 FROM mynewtable; which will allow you to have additional columns in the permanent table if required.

Collectives™ on Stack Overflow

Cannot create temporary tables in postgres using psycopg2

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related