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...