PostgreSQL bulk import from CSV failing. ERROR: invalid input syntax for integer: "1990-01-02" -

-

i importing data csv file postgresql table.

the table looks this:

create table foo_stats (                         td                      date not null,                         ins_id                  integer check (ins_id > 0) not null,                         df_id                   integer check (df_id > 0) not null,                         pc                      real not null default 0.0,                         ph                      real default 0.0 not null,                         pl                      real default 0.0 not null,                         av                      real default 0.0 not null,                         cv                      bigint default 0 not null,                         avi                     real default 0.0 not null,                         cmi                     bigint default 0 not null,                         vwp                     real check (vwp >= 0) not null,                         atr                     real not null default -99999,                         pv                      real not null default -99999,                         pr                      real not null default -99999,                          primary key                     (ins_id, df_id, td)                         );

before bulk copy, drop primary key using alter table drop constraint primary key

here trace of cl when invoke copy command @ psql cli:

mydb=# copy foo_stats '/path/to/data/foo_stats.csv' csv; error:  invalid input syntax integer: "1990-01-02" context:  copy foo_stats, line 1, column id: "1990-01-02" mydb=#

here first few lines of input csv file looks like:

"1990-01-02",388,3,-99999,0.913,0.91,0.0,0,0.0,0,0,-99999,-99999,-99999 "1990-01-02",388,4,-99999,0.913,0.91,0.0,0,0.0,0,0,-99999,-99999,-99999 "1990-01-02",388,1,-99999,0.913,0.91,0.0,0,0.0,0,0,-99999,-99999,-99999 "1990-01-02",388,7,-99999,0.913,0.91,0.0,0,0.0,0,0,-99999,-99999,-99999 "1990-01-02",388,6,-99999,0.913,0.91,0.0,0,0.0,0,0,-99999,-99999,-99999

the data columns in input file in same order in table schema. can explain why getting error message?

ps: using postgresql 8.4 on ubuntu 10.0.4 lts

cutting , pasting table definition , sample data here shows working.

so - either table definition or file not think are. if table columns in different order, can specify columns in copy command.

if file different, it's presumably not visible naked eye, or have spotted before asking. don't suppose file has come windows machine chance?

three things check for:

  1. unnecessary byte-order-mark (bom) @ start of file
  2. line-endings of \r\n rather \n
  3. any other control-codes

try cutting + pasting sample data , see if imports. if so, try opening real file in text editor , re-saving it. if fixes things, hex editor , take @ actual bytes in sample file.


Comments

Post a Comment

Popular posts from this blog

c# - SVN Error : "svnadmin: E205000: Too many arguments" -

-
i trying reposotries using c# code process svncommand = null; var psi = new processstartinfo("svnadmin"); psi.redirectstandardoutput = true; psi.redirectstandarderror = true; psi.useshellexecute = false; psi.arguments= @"dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump"; using (svncommand = process.start(psi)) { var myoutput = svncommand.standardoutput; var myerror = svncommand.standarderror; debug.write("output :" + environment.newline + environment.newline + myoutput.readtoend() + environment.newline + "error :" + environment.newline + environment.newline + myerror.readtoend()); svncommand.close(); } when use dump command commandline svnadmin dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump it works fine when try ...
Read more

c# - Copy ObservableCollection to another ObservableCollection -

-
how copy observablecollection item observablecollection without reference of first collection? here observablecollection item value changes affecting both collections. code private observablecollection<ratemodel> _allmetalrate = new observablecollection<ratemodel>(); private observablecollection<ratemodel> _metalrateondate = new observablecollection<ratemodel>(); public observablecollection<ratemodel> allmetalrate { { return this._allmetalrate; } set { this._allmetalrate = value; notifypropertychanged("metalrate"); } } public observablecollection<ratemodel> metalrateondate { { return this._metalrateondate; } set { this._metalrateondate = value; notifypropertychanged("metalrateondate"); } } foreach (var item in metalrateondate) allmetalrate.add(item); what causing , how can solve it? you need clone object referenced item before it's a...
Read more

All overlapping substrings matching a java regex -

-
is there api method returns (possibly overlapping) substrings match regular expression? for example, have text string: string t = 04/31 412-555-1235; , , have pattern: pattern p = new pattern("\\d\\d+"); matches strings of 2 or more characters. the matches are: 04, 31, 412, 555, 1235. how overlapping matches? i want code return: 04, 31, 41, 412, 12, 55, 555, 55, 12, 123, 1235, 23, 235, 35. theoretically should possible -- there obvious o(n^2) algorithm enumerates , checks substrings against pattern. edit rather enumerating substrings, safer use region(int start, int end) method in matcher . checking pattern against separate, extracted substring might change result of match (e.g. if there non-capturing group or word boundary check @ start/end of pattern). edit 2 actually, it's unclear whether region() expect zero-width matches. specification vague, , experiments yield disappointing results. for example: string line = "xx90xx"; strin...
Read more