Re: Typcasting

On Monday 06 Jan 2003 4:40 pm, Chris Boget wrote:
> I have a field in my table that is of type varchar. I'm trying
> to do a bit of tidying up and since that field contains only
> number, I want to convert the field to an integer type.

> I've looked up the CAST function and tried everything that I
> could think of but nothing seems to work.

I take it you're getting something like:

richardh=> select cast('123'::varchar as int4);
ERROR: Cannot cast type 'character varying' to 'integer'

You'll need to cast to text first, then to integer.

richardh=> select cast(cast('123'::varchar as text) as int4);
int4
------
123

So you'll have something like:

cast(cast(my_varchar_field as text) as int4)

The developers have reduced the number of casts to make expression behaviour
more predictable. This means you need to be more explicit about these things.
Can't help thinking that some form of varchar<=>text equivalence detection
would be useful IMHO.

--
Richard Huxton