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

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

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: