Thread: problem casting varchar to inet

select cast(coalesce(nullif('', trim(callingip)), '127.0.0.1') as inet)

ERROR:  invalid input syntax for type inet: ""

    what is it trying to tell me?

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

On Fri, Jul 22, 2005 at 02:20:31PM +0200, Roman Neuhauser wrote:
>
> select cast(coalesce(nullif('', trim(callingip)), '127.0.0.1') as inet)
>
> ERROR:  invalid input syntax for type inet: ""
>
>     what is it trying to tell me?

NULLIF returns the first argument if the arguments aren't equal,
so in this case if they aren't equal then NULLIF returns ''.  COALESCE
then returns '' because its first argument isn't NULL, and that
value is then cast to inet and fails.  Try reversing the order of
arguments to NULLIF:

select cast(coalesce(nullif(trim(callingip), ''), '127.0.0.1') as inet);

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

# mike@fuhr.org / 2005-07-22 06:50:58 -0600:
> On Fri, Jul 22, 2005 at 02:20:31PM +0200, Roman Neuhauser wrote:
> >
> > select cast(coalesce(nullif('', trim(callingip)), '127.0.0.1') as inet)
> >
> > ERROR:  invalid input syntax for type inet: ""
> >
> >     what is it trying to tell me?
>
> NULLIF returns the first argument if the arguments aren't equal,
> so in this case if they aren't equal then NULLIF returns ''.  COALESCE
> then returns '' because its first argument isn't NULL, and that
> value is then cast to inet and fails.  Try reversing the order of
> arguments to NULLIF:
>
> select cast(coalesce(nullif(trim(callingip), ''), '127.0.0.1') as inet);

    OMG I had it reversed... Thanks, and sorry for the bother.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991