PG COPY error: invalid input syntax for integer

Question

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

Answer 1

Just came across this while looking for a solution and wanted to add I was able to solve the issue by adding the "null" parameter to the copy_from call:

cur.copy_from(f, tablename, sep=',', null='')

Answer 2

I got this error when loading '|' separated CSV file although there were no '"' characters in my input file. It turned out that I forgot to specify FORMAT:

COPY ... FROM ... WITH (FORMAT CSV, DELIMITER '|').

Answer 3

Use the below command to copy data from CSV in a single line without casting and changing your datatype. Please replace "NULL" by your string which creating error in copy data

copy table_name from 'path to csv file' (format csv, null "NULL", DELIMITER ',', HEADER);

Answer 4

I had this same error on a postgres .sql file with a COPY statement, but my file was tab-separated instead of comma-separated and quoted.

My mistake was that I eagerly copy/pasted the file contents from github, but in that process all the tabs were converted to spaces, hence the error. I had to download and save the raw file to get a good copy.