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

User · Answer

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

User · Answer

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

User · Answer

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  quot hosturl quot   database  quot db name quot   user  quot username quot   password  quot password quot   cur   conn cursor   cur execute  quot CREATE TABLE schema destination table   quot               quot age integer   quot               quot first name varchar 20    quot               quot last name varchar 20  quot               quot    quot    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  quot  quot  quot COPY schema destination table FROM STDIN WITH  FORMAT CSV  quot  quot  quot   f

User · Answer

Ended up doing this using csvfix   csvfix map -fv    -tv  0   tmp people csv  gt   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  gt   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to postgresql

Examples related to csv

Examples related to import

Examples related to copy