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...
BUG #2866: cast varchar to decimal failure
| Tweet |
|
Search Discussions
-
Tom Lane at Dec 29, 2006 at 4:04 am ⇧ 
Why do you find that surprising? There's nothing there to guarantee"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 != '' )
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:While that happens to work at the moment, you shouldn't put any
select string1 from adfields where
(string1 != '' ) and (cast (string1 as decimal (6,2)) >= 0.0)
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
Related Discussions
Discussion Navigation
| view | thread | post |
Discussion Overview
| group | pgsql-bugs
 |
| categories | postgresql |
| posted | Dec 26, '06 at 8:00p |
| active | Dec 29, '06 at 4:04a |
| posts | 2 |
| users | 2 |
| website | postgresql.org |
| irc | #postgresql |
2 users in discussion
