to_number behavior change between 8.1 and 8.2

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

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

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