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)


Tue, 14 May 2002 13:56:33 -0500
Scott Royston <scroyston@mac.com>
can't cast varchar as integer?
> 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);

Tue, 14 May 2002 17:20:20 -0400
"Joel Burton" <joel@joelburton.com>
Re: can't cast varchar 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
14 May 2002 16:47:39 -0500
David Stanaway <david@stanaway.net>
Re: can't cast varchar as integer?
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
>


Tue, 14 May 2002 18:02:41 -0500
Scott Royston <scroyston@mac.com>
Re: [SQL] can't cast varchar as integer?
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.  So
	select 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.  But
	select 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

Tue, 14 May 2002 19:29:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: can't cast varchar as integer? 