Re: conversion

"Nathan Suderman" <nathan@pollstar.com> writes:
> Using version 7.0
> this is the error I get, quantity is of type varchar(9)
> store2=# select quantity::int from orderline ;
> ERROR:  Cannot cast type 'varchar' to 'int4'
> also does not work this way
> store2=# select cast(quantity as int) from orderline ;

> I was able to get around this by making two conversions
> store2=# select cast(cast(quantity as text) as int) from orderline ;
> or
> store2=# select (quantity::text)::int from orderline ;


Ah.  I was trying
    select int2(varcharvalue);
which does work, and is the recommended typeconversion notation if you
don't want to think hard.  The :: and cast() notations demand an *exact*
match between source and destination types.  As you discovered, the
actual path for this conversion is varchar::text::int2 (because there
is a conversion function int2(text), and the system knows that varchar
can be implicitly promoted to text).  The functional notation allows
you to be a little sloppy, the cast notation does not.  There have been
debates in the past about whether they shouldn't behave the same...
but I think sometimes it's useful to be able to control a type
conversion exactly.

            regards, tom lane