FAQ

BUG #2866: cast varchar to decimal failure

John lyssy
Dec 26, 2006 at 8:00 pm
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...
reply

Search Discussions

1 response

  • Tom Lane at Dec 29, 2006 at 4:04 am

    "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

Related Discussions

Discussion Navigation
viewthread | post

2 users in discussion

John lyssy: 1 post Tom Lane: 1 post