Importing CSV file into PostgreSQL

Question

Using MySQL Administrator GUI tool I have exported some data tables retrieved from an sql dumpfile to csv files.

I then tried to import these CSV files into a PostgreSQL database using the postgres COPY command. I've tried entering

COPY articles FROM '[insert .csv dir here]' DELIMITERS ',' CSV;

and also the same command without the delimiters part.

I get an error saying

ERROR: invalid input syntax for integer: "id"
CONTEXT: COPY articles, line 1, column id: "id"

In conclusion my question is what are some thoughts and solutions to this problem? Could it possibly be something to do with the way I created the csv files? or have I made a rookie mistake elsewhere?

I'm just wondering why you tagged your question as python? (am I missing something really obvious?) — brice
Write some scripts to populate the table, by reading your CSV file. — thegeek
Well I was open to some python suggestions since I did remove the non-ascii chars from the csv files using python, but yeah Gavin seemed to have solved my problem. Thanks for the input though. — user395558

Gavin Gavin · Accepted Answer · 2010-07-19T10:34:20

If you have header columns just add the header qualifier to the copy statement as per documentation to skip that line http://www.postgresql.org/docs/8.4/static/sql-copy.html

Importing CSV file into PostgreSQL

1 Answers