BUG #2866: cast varchar to decimal failure
The following bug has been logged online:
Bug reference: 2866
Logged by: john lyssy
Email address: jlyssy@missiontitle.com
PostgreSQL version: 8.2
Operating system: Windows XP
Description: cast varchar to decimal failure
Details:
This cast returns:ERROR: invalid input syntax for type numeric: ""
SQL state: 22P02
select string1 from adfields where
(cast (string1 as decimal (6,2)) >= 0.0) and (string1 != '' )
The cast works:
select string1 from adfields where
(string1 != '' ) and (cast (string1 as decimal (6,2)) >= 0.0)
The only thing changed is the order of where clause expressions!
Note: string1 is a varchar (50) which i am casting to decimal... e.g.
character '0' is casted to decimal 0, etc...
"john lyssy" <jlyssy@missiontitle.com> writes:
This cast returns:ERROR: invalid input syntax for type numeric: ""
select string1 from adfields where
(cast (string1 as decimal (6,2)) >= 0.0) and (string1 != '' )
Why do you find that surprising? There's nothing there to guarantee
that the string1 != '' condition will be checked before the cast is
attempted ... and indeed I think most people would say that the order
in which you wrote the conditions encourages the opposite.
The cast works:
select string1 from adfields where
(string1 != '' ) and (cast (string1 as decimal (6,2)) >= 0.0)
While that happens to work at the moment, you shouldn't put any
faith in it either, because in general AND does not guarantee
order of evaluation in SQL. If you want to be safe you need to
use a construct that does guarantee evaluation order, such as CASE:
select ... where
case when string1 != '' then cast (string1 as decimal (6,2)) >= 0.0
else false
end;
For more info see the fine manual:
http://www.postgresql.org/docs/8.2/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
regards, tom lane