Thread: conversion

how can I convert varchar to int?  I can not seem to cast the type (::int2) and there are no conversion functions in
thedocs (int(varchar)).  Is there a way I am overlooking?  basically I am trying to sum a field that contains all
numbersbut is set up as varchar. 

"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