Re: BUG #2866: cast varchar to decimal failure

"john lyssy" <jlyssy(at)missiontitle(dot)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