Skip site navigation (1) Skip section navigation (2)

Re: BUG #2866: cast varchar to decimal failure

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "john lyssy" <jlyssy(at)missiontitle(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2866: cast varchar to decimal failure
Date: 2006-12-29 04:04:41
Message-ID: 27865.1167365081@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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

In response to

pgsql-bugs by date

Next:From: Tigran MkrtchyanDate: 2006-12-29 09:09:02
Subject: Re: transactions getting slon in councurrent environment
Previous:From: Tom LaneDate: 2006-12-29 03:42:55
Subject: Re: transactions getting slon in councurrent environment

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group