Обсуждение: Extracting data from deprecated MONEY fields
I understand from http://www.postgresql.org/docs/8.0/static/datatype-money.html that the “money” data type is deprecated.
So I want to convert the data from my existing “money” columns into new un-deprecated columns, e.g. with type “decimal(10,2)”. But every SQL command I try tells me I can’t cast or convert “money” data into any other type I have tried, including decimal, numeric, varchar, and text.
Is there any way to do this?
~ TIA
~ Ken
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
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" ~ Ken > -----Original Message----- > From: Joshua D. Drake [mailto:jd@commandprompt.com] > 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 > >
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@commandprompt.com] > > 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@comcast.net
Thanks Adrian ~ See comments at end. > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Adrian Klaver > Sent: Friday, June 06, 2008 11:49 AM > To: pgsql-general@postgresql.org > Cc: Ken Winter > Subject: Re: [GENERAL] Extracting data from deprecated MONEY fields > > 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. ... > **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; > I'm indeed trying to get rid of US$ notation. Alas, here's what I get from trying the regexp pattern on my "amount" column (type=money): regexp_replace(amount::money::text, '[$,]', '', 'g')::numeric -> "cannot cast type money to text" regexp_replace(amount::text, '[$,]', '', 'g')::numeric -> "cannot cast type money to text" And if remove the cast to text, I get: regexp_replace(amount::money, '[$,]', '', 'g')::numeric -> " function regexp_replace(money, "unknown", "unknown", "unknown") does not exist" ~ Ken
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@comcast.net
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" > > ~ Ken >> 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. I think the steps Joshua is referring to are - 1. pg_dump -t mytable_with_money mydb > mytable_backup.sql 2. edit table definition in backup file to use numeric 3. remove $ and , from money column data 4. DROP TABLE mytable_with_money 5. psql < mytable_backup.sql While the data is in a text file regex tasks to remove the money formatting become a lot simpler. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
Hi, Shane Ambler wrote: ... > I think the steps Joshua is referring to are - > > 1. pg_dump -t mytable_with_money mydb > mytable_backup.sql > 2. edit table definition in backup file to use numeric > 3. remove $ and , from money column data > 4. DROP TABLE mytable_with_money > 5. psql < mytable_backup.sql > > While the data is in a text file regex tasks to remove the money > formatting become a lot simpler. to_char() and back to numeric shouldn't be a problem within the database and we have regex too if anything fails. I don't think you need to dump and edit the dump to achive that. Regards Tino
Вложения
Hi, Tino Wildenhain wrote: > Hi, > > Shane Ambler wrote: > ... >> I think the steps Joshua is referring to are - >> >> 1. pg_dump -t mytable_with_money mydb > mytable_backup.sql >> 2. edit table definition in backup file to use numeric >> 3. remove $ and , from money column data >> 4. DROP TABLE mytable_with_money >> 5. psql < mytable_backup.sql >> >> While the data is in a text file regex tasks to remove the money >> formatting become a lot simpler. > > to_char() and back to numeric shouldn't be a problem within the database > and we have regex too if anything fails. I don't think you need to > dump and edit the dump to achive that. Ah sorry forget that... neither to_char nor cast to text works. Really a horrible datatype :( Tino
Вложения
Tino Wildenhain <tino@wildenhain.de> writes: >> to_char() and back to numeric shouldn't be a problem within the database >> and we have regex too if anything fails. I don't think you need to >> dump and edit the dump to achive that. > Ah sorry forget that... neither to_char nor cast to text works. Really a > horrible datatype :( If you do it within plpgsql it should work. Just assign the money value to a text variable (or vice versa). regards, tom lane
Right you are, Tom! In case anyone else is facing the same migration, pasted in below is a pl/pgsql function that does the conversion. ~ Thanks to all ~ Ken > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Tom Lane > Sent: Saturday, June 07, 2008 11:25 AM > Subject: Re: [GENERAL] Extracting data from deprecated MONEY fields > ... > If you do it within plpgsql it should work. Just assign the money value > to a text variable (or vice versa). > > regards, tom lane CREATE OR REPLACE FUNCTION "public"."convert_money_column"(varchar, varchar, varchar) RETURNS varchar AS $BODY$ /* Converts the column given by arg 3 (in the table given by arg 2 in the schema given by arg 1) from a "money" type to a "numeric(10,2)" type, and repopulates the new column with the values from the old. Before doing that, it makes a backup of the original table, which should be deleted manually after verifying the results. */ DECLARE this_schema ALIAS FOR $1; this_table ALIAS FOR $2; this_column ALIAS FOR $3; q varchar := ''; q2 varchar := ''; rec record; this_oid oid; this_varchar varchar := ''; this_numeric numeric(10,2); n integer := 0; BEGIN q := 'CREATE TABLE ' || this_schema || '.' || this_table || '_bak' || ' AS SELECT * FROM ' || this_schema || '.' || this_table; EXECUTE q; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' ADD COLUMN ' || this_column || '_ money'; EXECUTE q; q := 'UPDATE ' || this_schema || '.' || this_table || ' SET ' || this_column || '_ = ' || this_column; EXECUTE q; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' DROP COLUMN ' || this_column || ' CASCADE'; EXECUTE q; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' ADD COLUMN ' || this_column || ' numeric(10,2)'; EXECUTE q; q := 'SELECT oid, ' || this_column || '_ AS money_column FROM ' || this_schema || '.' || this_table ; FOR rec IN EXECUTE q LOOP this_oid := rec.oid; this_varchar := rec.money_column; this_varchar := replace(this_varchar, '$', ''); this_varchar := replace(this_varchar, ',', ''); this_numeric := this_varchar; q2 := 'UPDATE ' || this_schema || '.' || this_table || ' SET ' || this_column || ' = ' || this_numeric || ' WHERE oid = ' || this_oid; EXECUTE q2; n := n + 1; END LOOP; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' DROP COLUMN ' || this_column || '_ CASCADE'; EXECUTE q; RETURN 'Did ' || n || ' records'; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
Here is a cast function I wrote some years ago to convert a couple of money columns to numeric http://zigo.org/postgresql/#cast_money_to_numeric You already have a solution, but maybe it is of value to someone else. /Dennis Ken Winter skrev: > Right you are, Tom! > > In case anyone else is facing the same migration, pasted in below is a > pl/pgsql function that does the conversion. > > ~ Thanks to all > ~ Ken >