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

Peninsula

It'd be so useful if it said which row/column was causing the issue :( – Followthrough

Answer 1

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

Shook

My 'pre-processing' was just search and replace for "" with a blank, but I only had a few rows, this might cause errors with larger CSV files, but worked fine for my simple use case. – Skit

an answer with "etc" and "whatever" shouldnt really be accepted – Perron

Answer 2

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

Hix

You can just use text type for all columns when importing. PostgreSQL doesn't care about varchar length anyway. – Reich

This may not work if first_name or last_name field contain coma ",". This is why "" is needed for some cases – Matronna

Answer 3

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

Maillot

This looks like a short snippet from Python, maybe you should provide a full example... – Canuck

Answer 4

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

Scalping

Answer 5

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

Annabell

Worked with me on pgAdmin 4.29 and PostgreSQL 12. – Hauge

Answer 6

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.

Mcmurray

Answer 7

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

Molotov

I guess you meant: delimiter ',' – Epileptic

Answer 8

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.

Peninsula

That's a link to the docs for an old version - latest is always at code.google.com/p/csvfix – Payday

I just did a search and replace for "" with a blank in the CSV – Skit

Answer 9

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;

Delaine

Answer 10

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

Trisomic

Answer 11

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)

Broody

Answer 12

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.

Diapason