Thread: to_number behavior change between 8.1 and 8.2
I am getting an error from to_number() in PostgreSQL 8.2 that does not occur in 8.1: invalid input syntax for type numeric: " " I assume that it is rejecting spaces, so I tried trimming with: to_number(trim (both ' ' from streetnum),'999999999') but still get the same error. Any suggestions? Thanks, Rich -- Richard Greenwood richard.greenwood@gmail.com www.greenwoodmap.com
Richard Greenwood wrote: > I am getting an error from to_number() in PostgreSQL 8.2 that does not > occur in 8.1: > invalid input syntax for type numeric: " " > I assume that it is rejecting spaces, so I tried trimming with: > to_number(trim (both ' ' from streetnum),'999999999') > but still get the same error. In 8.2 we no longer accept anything but valid numbers for numbers :). "" is not a valid number. The way around this is to change "" to NULL . Sincerely, Joshua D. Drake > > Any suggestions? > > Thanks, > Rich >
Richard Greenwood wrote: > I am getting an error from to_number() in PostgreSQL 8.2 that does not > occur in 8.1: > invalid input syntax for type numeric: " " > I assume that it is rejecting spaces, so I tried trimming with: > to_number(trim (both ' ' from streetnum),'999999999') > but still get the same error. > > Any suggestions? I seem to recall it was tightened up - you'll need to give it a valid number. What number is '' supposed to be? -- Richard Huxton Archonet Ltd
On 5/16/07, Joshua D. Drake <jd@commandprompt.com> wrote: > Richard Greenwood wrote: > > I am getting an error from to_number() in PostgreSQL 8.2 that does not > > occur in 8.1: > > invalid input syntax for type numeric: " " > > I assume that it is rejecting spaces, so I tried trimming with: > > to_number(trim (both ' ' from streetnum),'999999999') > > but still get the same error. > > In 8.2 we no longer accept anything but valid numbers for numbers :). "" > is not a valid number. > > The way around this is to change "" to NULL . > > Sincerely, > > Joshua D. Drake Thanks for the replies. The old behavior was certainly handier for my application. I had been using the old to_number() to get a numeric sort on a char field of street address "numbers" that contained spaces and "numbers" like '502B'. So with 8.2, I could: 1. pre-process (clean up) the data so that it really was numeric 2. write my own function duplicating the 8.1 behavior 3. or, any other ideas? Thanks again, Rich -- Richard Greenwood richard.greenwood@gmail.com www.greenwoodmap.com