"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