Invalid input syntax for integer ошибка

oldhomemovieoldhomemovie

14.5k13 gold badges64 silver badges98 bronze badges

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

answered Aug 18, 2013 at 10:57

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

answered Aug 18, 2013 at 10:58

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

answered Sep 19, 2019 at 12:38

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 ‘|’).

answered Mar 11, 2018 at 0:09

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

answered Dec 31, 2019 at 9:25

zwippiezwippie

14.9k3 gold badges38 silver badges54 bronze badges

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

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.

answered Aug 18, 2013 at 14:10

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;

answered Aug 24, 2017 at 18:05

Charlie 木匠Charlie 木匠

2,17419 silver badges19 bronze badges

Theo FTheo F

1,1571 gold badge11 silver badges18 bronze badges

user3507825user3507825

4285 silver badges13 bronze badges

Use the NULL parameter. From the documentation:

NULL

Specifies the string that represents a null value. The default is N (backslash-N) in text format, and an unquoted empty string in CSV format.

Example

copy my_table from '/my/csv_file.csv' (format csv, null 'N');

answered Oct 24, 2017 at 16:27