conversion

"Nathan Suderman" <nathan@pollstar.com> writes:

how can I convert varchar to int? I can not seem to cast the type
(::int2) and there are no conversion functions in the docs
(int(varchar)).

What Postgres version are you using? This works fine for me in 7.0
and later...

regards, tom lane

"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