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; > > ~ Ken > > > -----Original Message----- > > From: Joshua D. Drake [mailto:jd@xxxxxxxxxxxxxxxxx] > > Sent: Thursday, June 05, 2008 11:22 PM > > To: Ken Winter > > Cc: PostgreSQL pg-general List > > Subject: Re: [GENERAL] Extracting data from deprecated MONEY fields > > > > Ken Winter wrote: > > > I understand from > > > http://www.postgresql.org/docs/8.0/static/datatype-money.html that the > > > "money" data type is deprecated. > > > > Money is no longer deprecated in newer releases (specifically 8.3), > > although I do think it would be wise to push it to numeric. > > > > I think the way to do it would be to backup the table and edit the table > > definition from the file. Make the money a numeric. Then reload the > > table from the backup. > > > > Sincerely, > > > > Joshua D. Drake -- Adrian Klaver aklaver@xxxxxxxxxxx
