On 5/16/07, Joshua D. Drake <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED]
www.greenwoodmap.com
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
