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-27 11:14:32 |
Message-ID: | 20011127221432.A25620@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Nov 27, 2001 at 09:55:41AM -0000, Andy Marden wrote:
> Just undertaking a port of a small Data Warehouse-type batch load
> application from Oracle to PostgreSQL. It's common practice in such a system
> to load data into a staging table whose fields are typically all varying
> text. Then the conversion and mapping of the data is done as a second stage
> into the 'proper' tables.
>
> In Oracle, you can put a text field into a numeric field as long as it
> contains a number and teh conversion will be implicit (or you can simply
> specify to_number(value)) to make the conversion explicitly.
>
> In PostgreSQL, it seems that an error is thrown up if an implicit conversion
> is attempted, and the only explicit conversion seems to be to_number(value,
> format) which doesn't do what's needed generically enough.
>
> I can't believe that this isn't possible - can anyone point me in the right
> direction?
Well, it's a bit more complicated than that. For example, text -> int4 is
done. You do realise you can cast like value::type. For example:
select field::numeric(10,2);
Now, I'm not sure if you can do varchar -> numeric directly. I don't use
varchar anymore since there is no advantage over text but text is much
better supported.
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 | Karel Zak | 2001-11-27 11:21:45 | Re: Casting Varchar to Numeric |
Previous Message | kingking | 2001-11-27 10:18:15 | Backup Large Object |