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.