Re: Casting Varchar to Numeric

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
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(!)

Thanks for your help.

Andy

"Martijn van Oosterhout" <kleptog(at)svana(dot)org> wrote in message
news:20011129105642(dot)A31599(at)svana(dot)org(dot)(dot)(dot)
> On Wed, Nov 28, 2001 at 04:30:00PM -0000, Andy Marden wrote:
> > Well, I've finally sorted it out and can now convert text/varchar fields
> > into numeric I cannot BELIEVE I've had to resort to such things.
> >
> > I've changed all my varchar fields to text and then applied this to them
> > (column name is 'litre_amount'):
>
> <snip ugly conversion method>
>
> Does this work?
>
> select litre_amount::float::numeric;
>
> With the column still as varchar, this worked for me:
>
> select litre_amount::text::float::numeric from temp2;
>
> Long-winded definitly. You can create your own conversion function to
> automaticaly convert text -> numeric. <untested!>
>
> create function numeric(text) returns numeric
> as 'select $1::float8::numeric' language 'sql';
>
> The problem is that postgres has an extrememly generic type system and it
> has no idea about promoting types. For example, you get a problem when
> comparing an int4 to an int8. Should you convert both arguments to int4's
or
> int8's? *We* know the answer but postgres doesn't.
>
> This problem extends to anywhere where multiple types do similar things:
>
> int2, int4, int8
> float4, float8, numeric
> text, varchar, char
>
> HTH,
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org>
> http://svana.org/kleptog/
> > Terrorists can only take my life. Only my government can take my
freedom.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html