Re: Casting Varchar to Numeric - Mailing list pgsql-general

From Martijn van Oosterhout
Subject Re: Casting Varchar to Numeric
Date
Msg-id 20011129105642.A31599@svana.org
Whole thread Raw
In response to Re: Casting Varchar to Numeric  ("Andy Marden" <amarden@usa.net>)
Responses Re: Casting Varchar to Numeric
List 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@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: error on drop table
Next
From: Joseph Shraibman
Date:
Subject: Re: error on drop table