Re: Casting Varchar to Numeric

> On Mon, 3 Dec 2001, Andy Marden wrote:
>
> > Martijn,
> >
> > It does work (believe it or not). I've now tried the method you mention
> > below - that also works and is much nicer. I can't believe that PostgreSQL
> > can't work this out. Surely implementing an algorithm that understands that
> > if you can go from a ->b and b->c then you can certainly go from a->c. If
>
> It's more complicated than that (and postgres does some of this but not
> all), for example the cast text->float8->numeric potentially loses
> precision and should probably not be an automatic cast for that reason.
>
> > this is viewed as too complex a task for the internals - at least a diagram
> > or some way of understanding how you should go from a->c would be immensely
> > helpful wouldn't it! Daunting for anyone picking up the database and trying
> > to do something simple(!)
>
> There may be a need for documentation on this. Would you like to write
> some ;)

OK, I ran some tests:

test=> create table test (x text);
CREATE
test=> insert into test values ('323');
INSERT 5122745 1
test=> select cast (x as numeric) from test;
ERROR: Cannot cast type 'text' to 'numeric'

I can see problems with automatically casting numeric to text because
you have to guess the desired format, but going from text to numeric
seems quite easy to do. Is there a reason we don't do it?

I can cast to integer and float8 fine:

test=> select cast ( x as integer) from test;
?column?
----------
323
(1 row)

test=> select cast ( x as float8) from test;
?column?
----------
323
(1 row)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026