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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026