On Friday 06 June 2008 8:49 am, Adrian Klaver wrote: > On Friday 06 June 2008 8:25 am, Ken Winter wrote: > > Thanks, Joshua ~ > > > > What you suggest is basically what I'm trying to do. Where I'm stuck is > > in finding a construct (a CAST or whatever) to turn the existing "money" > > column data (directly or indirectly) into numeric. I've tried to convert > > a column named "amount" in the following ways, with the following > > results: > > > > CAST(amount AS numeric) -> "cannot cast type money to numeric" > > CAST(amount AS numeric(10,2)) -> "cannot cast type money to numeric" > > CAST(amount AS decimal) -> "cannot cast type money to numeric" > > CAST(amount AS text) -> "cannot cast type money to text" > > CAST(amount AS varchar) -> "cannot cast type money to character varying" > > to_char(money) -> "function to_char(money) does not exist" > > I don't know if this helps. > From docs; > http://www.postgresql.org/docs/8.3/interactive/datatype-money.html > The money type stores a currency amount with a fixed fractional precision; > see Table 8-3. Input is accepted in a variety of formats, including integer > and floating-point literals, as well as "typical" currency formatting, such > as '$1,000.00'. Output is generally in the latter form but depends on the > locale. Non-quoted numeric values can be converted to money by casting the > numeric value to text and then money: > > SELECT 1234::text::money; > > **There is no simple way of doing the reverse in a locale-independent > manner, namely casting a money value to a numeric type. If you know the > currency symbol and thousands separator you can use regexp_replace(): > > SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric; > Oops this only works with 8.3, I forgot you where working with 8.0. I tried the dump and restore that Josh suggested and that did not work as it choked on the $ sign. The only way I could get it to work was to remove the $ from the dumped data. -- Adrian Klaver aklaver@xxxxxxxxxxx
