Re: Casting Varchar to Numeric

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@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.