Re: 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(at)commandprompt(dot)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(at)commandprompt(dot)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(at)comcast(dot)net

Thanks Adrian ~ See comments at end.

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Adrian Klaver
> Sent: Friday, June 06, 2008 11:49 AM
> To: pgsql-general(at)postgresql(dot)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(at)comcast(dot)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(at)wildenhain(dot)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(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)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
>