CODEWITHSUNDEEP
pythonpostgresql
emax
emax

Reputation: 7235

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

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;

enter image description here

Upvotes: 0

Views: 750

Answers (1)

francisco sollima
francisco sollima

Reputation: 8338

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.

Upvotes: 1

Related Questions