Postgresql: how to copy a column from a table to another using psycopg2?

Question

I am using psycopg2 in python to manage my database.

I created two tables. One called data that I want to populate with some data. For doing so I created a temporary table called temporarytable from a .csv file.

I want to copy the column numberofinhabitants. So what I am doing is the following:

### Add Column
import psycopg2 as ps    
stat1 = """  ALTER TABLE data ADD COLUMN numberofinhabitants integer"""
con = ps.connect(dbname = 'mydb', user='postgres', host='localhost', password='mypd')
con.autocommit = True
cur = con.cursor()
cur.execute(stat1)
cur.close()
con.close()


### Copy Column
stat2 = """INSERT INTO data (numberofinhabitants) SELECT numberofinhabitants FROM temporarytable"""
con = ps.connect(dbname = 'mydb', user='postgres', host='localhost', password='mypd')
con.autocommit = True
cur = con.cursor()
cur.execute(stat2)
cur.close()
con.close()

but I get the following error

ProgrammingError: column "numberofinhabitants" does not exist
LINE 1: INSERT INTO data (numberofinhabitants) SELECT numberofinhabi...
                                                      ^
HINT:  There is a column named "numberofinhabitants" in table "data", but it cannot be referenced from this part of the query.

Below a screenshot from pgAdmin3 after SELECT * FROM temporarytable;

Where are you creating temporarytable? Can't it be that the table, being temporary, is already deleted by the time you try to copy? — francisco sollima
– francisco sollima, Commented Sep 14, 2017 at 14:10
Maybe you shouldn't close the connection and reopen it (that may remove the temp table) or you should create a regular table. — francisco sollima
– francisco sollima, Commented Sep 14, 2017 at 14:11
@franciscosollima it is a regular table that I called temporarytable — emax
– emax, Commented Sep 14, 2017 at 14:13

francisco sollima · Accepted Answer · 2017-09-14 14:28:51Z

1

I think the problem is that PostgreSQL's columns are case sensitive. You should try this as stat2:

stat2 = """INSERT INTO data (numberofinhabitants) SELECT "numberOfInhabitants" FROM temporarytable"""

Note that you should also use " for columns with upper characters in them.

answered Sep 14, 2017 at 14:28

francisco sollima

8,4184 gold badges25 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

Postgresql: how to copy a column from a table to another using psycopg2?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related