Thread: can't cast varchar as integer?

Mac OSX, postgresql 7.2.1

what's the reasoning behind not being able to cast a varchar as 
integer?  this seems very weird to me:

LEDEV=# create table test (foo as varchar(5), bar as text);
ERROR:  parser: parse error at or near "as"
LEDEV=# create table test (foo varchar(5), bar text);
CREATE
LEDEV=# insert into test (foo, bar) values ('123', '123');
INSERT 409490 1
LEDEV=# select * from test; foo | bar
-----+----- 123 | 123
(1 row)

LEDEV=# select cast(foo as integer) from test;
ERROR:  Cannot cast type 'character varying' to 'integer'
LEDEV=# select cast(bar as integer) from test; bar
----- 123
(1 row)

> what's the reasoning behind not being able to cast a varchar as 
> integer?  this seems very weird to me:
> 
> LEDEV=# select cast(foo as integer) from test;
> ERROR:  Cannot cast type 'character varying' to 'integer'
> LEDEV=# select cast(bar as integer) from test;
>   bar
> -----
>   123
> (1 row)

Interesting. You can have an intermediate to-text cast:

select cast ( cast ( cast ('123' as varchar) as text) as integer);

On Tue, 2002-05-14 at 13:56, Scott Royston wrote:
> Mac OSX, postgresql 7.2.1
>
> what's the reasoning behind not being able to cast a varchar as
> integer?  this seems very weird to me:
>
> LEDEV=# create table test (foo varchar(5), bar text);
> LEDEV=# insert into test (foo, bar) values ('123', '123');
> LEDEV=# select cast(foo as integer) from test;
> ERROR:  Cannot cast type 'character varying' to 'integer'
> LEDEV=# select cast(bar as integer) from test;
>   bar
> -----
>   123
> (1 row)


Try this:

scratch=# select foo::text::integer from test;foo
-----123
(1 row)


Or:

scratch=# select int4(foo) from test;int4
------ 123
(1 row)



--
David Stanaway

Thanks for the replies so far.  I had been using cast(foo::text as 
integer).

To clarify my question, does anyone know *why* I can't cast from varchar 
to integer?  Why should I have to cast to text first?

thanks


On Tuesday, May 14, 2002, at 04:47 PM, David Stanaway wrote:

> On Tue, 2002-05-14 at 13:56, Scott Royston wrote:
>> Mac OSX, postgresql 7.2.1
>>
>> what's the reasoning behind not being able to cast a varchar as
>> integer?  this seems very weird to me:
>>
>> LEDEV=# create table test (foo varchar(5), bar text);
>> LEDEV=# insert into test (foo, bar) values ('123', '123');
>> LEDEV=# select cast(foo as integer) from test;
>> ERROR:  Cannot cast type 'character varying' to 'integer'
>> LEDEV=# select cast(bar as integer) from test;
>>   bar
>> -----
>>   123
>> (1 row)
>
>
> Try this:
>
> scratch=# select foo::text::integer from test;
>  foo
> -----
>  123
> (1 row)
>
>
> Or:
>
> scratch=# select int4(foo) from test;
>  int4
> ------
>   123
> (1 row)
>
>
>
> --
> David Stanaway
>

David Stanaway <david@stanaway.net> writes:
>> LEDEV=3D# select cast(foo as integer) from test;
>> ERROR:  Cannot cast type 'character varying' to 'integer'

> scratch=3D# select foo::text::integer from test;
> [works]

> scratch=3D# select int4(foo) from test;
> [works]

For reasons that I don't entirely recall at the moment (but they seemed
good to the pghackers list at the time), cast notations only work if
there is a cast function *exactly* matching the requested cast.
On the other hand, the functional form is laxer because there's an
allowed step of implicit coercion before the function call.

In the case at hand, there's a text->int4 cast function (look in
pg_proc, you'll see int4(text)) but there's no int4(varchar) function.
Also, varchar can be cast to text implicitly --- this is actually
a "binary equivalent" cast requiring no run-time effort.  Soselect foo::text::integer from test;
works: it's a binary-equivalent cast from varchar to text, followed
by application of int4(text).  And select int4(foo) from test;
works because the same function is found and implicit coercion of
its argument to text succeeds.  Butselect cast(foo as integer) from test;
doesn't work because there's no declared function int4(varchar).

There's probably not any good reason why there's not int4(varchar),
just that no one got around to making one.
        regards, tom lane