PG COPY error: invalid input syntax for integer
Solution 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, PerlText::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
Solution 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
Solution 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='')
Solution 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 '|').
Solution 5
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.
gmile
Updated on July 05, 2022Comments
-
gmile almost 2 years
Running
COPY
results inERROR: 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
-
Admin over 10 yearsThat's a link to the docs for an old version - latest is always at code.google.com/p/csvfix
-
icc97 almost 6 yearsI just did a search and replace for
""
with a blank in the CSV -
icc97 almost 6 yearsMy '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. -
Nux over 5 yearsYou can just use
text
type for all columns when importing. PostgreSQL doesn't care aboutvarchar
length anyway. -
Andi over 3 yearsThis looks like a short snippet from Python, maybe you should provide a full example...
-
Cortex over 3 yearsWorked with me on pgAdmin 4.29 and PostgreSQL 12.
-
Bastiaan Wakkie over 2 yearsI guess you meant: delimiter ','