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.