I'm writing the code in Python and am using the psycopg2 library to deal with my PostgreSQL database.
I have the table Orders with about 30 rows. I want to copy this entire table to an empty one with the same structure called Orders2.
How can I do this?
Thank you.
The answer is:
import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE
import sys
import cStringIO
con = psycopg2.connect(database="xxxx", user="xxxx", password="xxxx", host="localhost")
cur = con.cursor()
input = cStringIO.StringIO()
cur.copy_expert('COPY (select * from Orders) TO STDOUT', input)
input.seek(0)
cur.copy_expert('COPY Orders2 FROM STDOUT', input)
con.commit()
While your own answer is correct, there is a better way that does not involve dumping to a file and is generally more flexible.
INSERT INTO orders2 SELECT * FROM orders
