[postgresql] PG COPY error: invalid input syntax for integer

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

this ought to work without you modifying the source csv file:

alter table people alter column age type text;
copy people from '/tmp/people.csv' with csv;

I think it's better to change your csv file like:

"age","first_name","last_name"
23,Ivan,Poupkine
,Eugene,Pirogov

It's also possible to define your table like

CREATE TABLE people (
  age        varchar(20),
  first_name varchar(20),
  last_name  varchar(20)
);

and after copy, you can convert empty strings:

select nullif(age, '')::int as age, first_name, last_name
from people

Incredibly, my solution to the same error was to just re-arrange the columns. For anyone else doing the above solutions and still not getting past the error.

I apparently had to arrange the columns in my CSV file to match the same sequence in the table listing in PGADmin.

There is a way to solve "", the quoted null string as null in integer column, use FORCE_NULL option :

\copy table_name FROM 'file.csv' with (FORMAT CSV, FORCE_NULL(column_name));

see postgresql document, https://www.postgresql.org/docs/current/static/sql-copy.html

Ended up doing this using csvfix:

csvfix map -fv '' -tv '0' /tmp/people.csv > /tmp/people_fixed.csv

In case you know for sure which columns were meant to be integer or float, you can specify just them:

csvfix map -f 1 -fv '' -tv '0' /tmp/people.csv > /tmp/people_fixed.csv

Without specifying the exact columns, one may experience an obvious side-effect, where a blank string will be turned into a string with a 0 character.

All in python (using psycopg2), create the empty table first then use copy_expert to load the csv into it. It should handle for empty values.

import psycopg2
conn = psycopg2.connect(host="hosturl", database="db_name", user="username", password="password")
cur = conn.cursor()
cur.execute("CREATE TABLE schema.destination_table ("
            "age integer, "
            "first_name varchar(20), "
            "last_name varchar(20)"
            ");")

with open(r'C:/tmp/people.csv', 'r') as f:
    next(f)  # Skip the header row. Or remove this line if csv has no header.
    conn.cursor.copy_expert("""COPY schema.destination_table FROM STDIN WITH (FORMAT CSV)""", f)

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='')

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.

CREATE TABLE people (
  first_name varchar(20),
  age        integer,
  last_name  varchar(20)
);

"first_name","age","last_name" Ivan,23,Poupkine Eugene,,Pirogov

copy people from 'file.csv' with (delimiter ';', null '');

select * from people;

Just in first column.....

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);

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 '|').