From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Andy Marden <amarden(at)usa(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Casting Varchar to Numeric |
Date: | 2001-11-28 23:56:42 |
Message-ID: | 20011129105642.A31599@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-11-28 23:59:49 | Re: error on drop table |
Previous Message | Joseph Shraibman | 2001-11-28 23:19:25 | error on drop table |