PG COPY error: invalid input syntax for integer
copy, csv, import, postgresql
Solution
ERROR: invalid input syntax for integer: ""
`""` isn't a valid integer. PostgreSQL accepts unquoted blank fields as null by default in CSV, but `""` would be like writing:
SELECT ''::integer;
and fail for the same reason.
If you want to deal with CSV that has things like quoted empty strings for null integers, you'll need to feed it to PostgreSQL via a pre-processor that can neaten it up a bit. PostgreSQL's CSV input doesn't understand all the weird and wonderful possible abuses of CSV.
Options include:
- Loading it in a spreadsheet and exporting sane CSV;
- Using the Python `csv` module, Perl `Text::CSV`, etc to pre-process it;
- Using Perl/Python/whatever to load the CSV and insert it directly into the DB
- Using an ETL tool like CloverETL, Talend Studio, or Pentaho Kettle
Problem
Running `COPY` results in `ERROR: invalid input syntax for integer: ""` error message for me. What am I missing? My `/tmp/people.csv` file: ``` "age","first_name","last_name" "23","Ivan","Poupkine" "","Eugene","Pirogov" ``` My `/tmp/csv_test.sql` file: ``` CREATE TABLE people ( age integer, first_name varchar(20), last_name varchar(20) ); COPY people FROM '/tmp/people.csv' WITH ( FORMAT CSV, HEADER true, NULL '' ); DROP TABLE people; ``` Output: ``` $ psql postgres -f /tmp/sql_test.sql CREATE TABLE psql:sql_test.sql:13: ERROR: invalid input syntax for integer: "" CONTEXT: COPY people, line 3, column age: "" DROP TABLE ``` Trivia: - PostgreSQL 9.2.4