argh... can't cast varchar to int

From: Matthew Nuzum <cobalt(at)bearfruit(dot)org>
To: Postgres General List <pgsql-general(at)postgresql(dot)org>
Subject: argh... can't cast varchar to int
Date: 2002-11-25 02:28:11
Message-ID: 1038191293.3524.119.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table that has (among other things) two values, one named
ftr_type and the other ftr_data.

Depending on the value of ftr_type, ftr_data may contain the value of a
primary key in another table. Because sometimes there is another table
and sometimes there isn't, ftr_data is a varchar.

I'm writing some SQL functions that will help me maintain referential
integrity, but they won't let me use the value in ftr_data as a
reference to the other table. It says:

Cannot cast type 'character varying' to 'integer'

Here's what I've tried:
select CAST(ftr_data as int4) from pages where pageid = 2783;
ERROR: Cannot cast type 'character varying' to 'integer'

select ftr_data::int4 from pages where pageid = 2783;
ERROR: Cannot cast type 'character varying' to 'integer'

select to_num(ftr_data) from pages where pageid = 2783;
ERROR: Function 'to_num(varchar)' does not exist
Unable to identify a function that satisfies the given argument
types
You may need to add explicit typecasts

Just for reference, here's what the data in ftr_data looks like in this
case:
select ftr_data from pages where pageid = 2783;
ftr_data
----------
22

Also for reference, this doesn't work:
select CAST('22'::varchar as int4);
ERROR: Cannot cast type 'character varying' to 'integer'

Can anyone suggest a way that I can do this in my pl/pgsql function?
Thanks,
Matthew Nuzum

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oliver Elphick 2002-11-25 02:47:57 Re: argh... can't cast varchar to int
Previous Message Jean-Christian Imbeault 2002-11-25 01:31:50 Re: Calculated fileds in pg