Thread: typecasting numeric(18,4) to varchar/text

hi
how can i typecast a numeric(18,4) value (that i'm extracting from a table)
into a a varchar (which i'll be inserting into another table)
that is:

create table a (a numeric(18,4));
create table b (b varchar(25));
insert into a values(12000.43);
insert into b select (a.a)::varchar;


the above gives the followig error

ERROR:  Cannot cast type 'numeric' to 'character varying'

alternately try (this is my real requirement)

insert into b select 'ID'||((a.a)::varchar);

there has been no results on google

thanks in advance
ashok

--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .

On Tue, May 04, 2004 at 09:35:31AM -0400, ashok@kalculate.com wrote:
> create table a (a numeric(18,4));
> create table b (b varchar(25));
> insert into a values(12000.43);
> insert into b select (a.a)::varchar;

Which version is that?  Here's my session:

andrewtest=# create table a (a numeric(18,4));
CREATE TABLE
andrewtest=# create table b (b varchar(25));
CREATE TABLE
andrewtest=# insert into a values(12000.43);
INSERT 17168 1
andrewtest=# insert into b select (a.a)::varchar;
INSERT 17169 1

That's on 7.4.2.  You might want to try casting to text first.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca

Am 04.05.2004 16:37 schrieb Andrew Sullivan:

> On Tue, May 04, 2004 at 09:35:31AM -0400, ashok@kalculate.com wrote:
> 
>>create table a (a numeric(18,4));
>>create table b (b varchar(25));
>>insert into a values(12000.43);
>>insert into b select (a.a)::varchar;
> 
> 
> Which version is that?  Here's my session:
> 
> andrewtest=# create table a (a numeric(18,4));
> CREATE TABLE
> andrewtest=# create table b (b varchar(25));
> CREATE TABLE
> andrewtest=# insert into a values(12000.43);
> INSERT 17168 1
> andrewtest=# insert into b select (a.a)::varchar;
> INSERT 17169 1
> 
> That's on 7.4.2.  You might want to try casting to text first.
> 

Yes, it also works for me with Version 7.4.2.
I found an older postgres DB with Version 7.2 and I get the same
error as ashok

demo=# select a::varchar from a;
ERROR:  Cannot cast type 'numeric' to 'character varying'

demo=# select a::text from a;
ERROR:  Cannot cast type 'numeric' to 'text'

demo=# select '\'' || a || '\'' from a;;
ERROR:  Unable to identify an operator '||' for types 'unknown'
and 'numeric'
You will have to retype this query using an explicit cast

Greetings,
Martin


-- 
Martin Knipper
www  : http://www.mk-os.de
Mail : knipper@mk-os.de

Random Signature:
-----------------
while( !asleep() ) sheep++;