Thread: [GENERAL] Migrating money column from MS SQL Server to Postgres

[GENERAL] Migrating money column from MS SQL Server to Postgres

From
"Igal @ Lucee.org"
Date:

Hello,

I am migrating a database from MS SQL Server to Postgres. 

I have a column named "discount" of type money in SQL Server.  I created the table in Postgres with the same name and type, since Postgres has a type named money, and am transferring the data by using PDI (Pentaho Data Integration) Kettle/Spoon.

Kettle throws an error though:  column "discount" is of type money but expression is of type double precision.

The value in the offending insert is:  0.0

Why does Postgres decide that 0.0 is "double precision" (which is a weird name in my opinion -- why can't it just be double) and not money?

I have control over the SELECT but not over the INSERT.  Is there any way to set the cast the value on the SELECT side in MS SQL Server to specify the column type of Postgres-money?

The only solution I found is to set the column in Postgres to DOUBLE PRECISION instead of MONEY, but I'm not sure if there are negative side effects to that?

Igal Sapir
Lucee Core Developer
Lucee.org

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
Tom Lane
Date:
"Igal @ Lucee.org" <igal@lucee.org> writes:
> I have a column named "discount" of type money in SQL Server.  I created
> the table in Postgres with the same name and type, since Postgres has a
> type named money, and am transferring the data by using PDI (Pentaho
> Data Integration) Kettle/Spoon.

> Kettle throws an error though:  column "discount" is of type money but
> expression is of type double precision.

> The value in the offending insert is:  0.0

> Why does Postgres decide that 0.0 is "double precision" (which is a
> weird name in my opinion -- why can't it just be double) and not money?

Kettle must be telling it that --- on its own, PG would think '0.0'
is numeric, which it does have a cast to money for.

regression=# create table m (m1 money);
CREATE TABLE
regression=# insert into m values (0.0);
INSERT 0 1
regression=# insert into m values (0.0::numeric);
INSERT 0 1
regression=# insert into m values (0.0::float8);
ERROR:  column "m1" is of type money but expression is of type double precision
LINE 1: insert into m values (0.0::float8);                             ^
HINT:  You will need to rewrite or cast the expression.

You'll need to look at the client-side code to see where it's going wrong.

> The only solution I found is to set the column in Postgres to DOUBLE
> PRECISION instead of MONEY, but I'm not sure if there are negative side
> effects to that?

Well, it's imprecise.  Most people don't like that when it comes to
monetary amounts.
        regards, tom lane


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
"David G. Johnston"
Date:
On Wednesday, November 8, 2017, Igal @ Lucee.org <igal@lucee.org> wrote:

Kettle throws an error though:  column "discount" is of type money but expression is of type double precision.

The value in the offending insert is:  0.0

Why does Postgres decide that 0.0 is "double precision" (which is a weird name in my opinion -- why can't it just be double) and not money?

The lack of quotes surrounding the value is significant.  Money input requires a string literal.  Only (more or less) integer and double literal values can be written without the single quotes.

David J.

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
Allan Kamau
Date:


On Nov 9, 2017 03:46, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
"Igal @ Lucee.org" <igal@lucee.org> writes:
> I have a column named "discount" of type money in SQL Server.  I created
> the table in Postgres with the same name and type, since Postgres has a
> type named money, and am transferring the data by using PDI (Pentaho
> Data Integration) Kettle/Spoon.

> Kettle throws an error though:  column "discount" is of type money but
> expression is of type double precision.

> The value in the offending insert is:  0.0

> Why does Postgres decide that 0.0 is "double precision" (which is a
> weird name in my opinion -- why can't it just be double) and not money?

Kettle must be telling it that --- on its own, PG would think '0.0'
is numeric, which it does have a cast to money for.

regression=# create table m (m1 money);
CREATE TABLE
regression=# insert into m values (0.0);
INSERT 0 1
regression=# insert into m values (0.0::numeric);
INSERT 0 1
regression=# insert into m values (0.0::float8);
ERROR:  column "m1" is of type money but expression is of type double precision
LINE 1: insert into m values (0.0::float8);
                              ^
HINT:  You will need to rewrite or cast the expression.

You'll need to look at the client-side code to see where it's going wrong.

> The only solution I found is to set the column in Postgres to DOUBLE
> PRECISION instead of MONEY, but I'm not sure if there are negative side
> effects to that?

Well, it's imprecise.  Most people don't like that when it comes to
monetary amounts.

                        regards, tom lane


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Could try using NUMERIC datatype for such a field. 


Allan 

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
"Igal @ Lucee.org"
Date:
Thank you all for your help:

On 11/8/2017 4:45 PM, Tom Lane wrote:
"Igal @ Lucee.org" <igal@lucee.org> writes:
The value in the offending insert is:  0.0

Why does Postgres decide that 0.0 is "double precision" (which is a 
weird name in my opinion -- why can't it just be double) and not money?
Kettle must be telling it that --- on its own, PG would think '0.0'
is numeric, which it does have a cast to money for.

Looks like you are correct.  Kettle shows me the INSERT statement and when I execute it outside of Kettle (in a regular SQL client), the INSERT succeeds.

On 11/8/2017 4:45 PM, David G. Johnston wrote:

The lack of quotes surrounding the value is significant.  Money input requires a string literal.  Only (more or less) integer and double literal values can be written without the single quotes.

That didn't work.  I CAST'ed the value in the SELECT to VARCHAR(16) but all it did was change the error message to say that it expected `money` but received `character varying`.

On 11/8/2017 4:52 PM, Allan Kamau wrote:

 On Nov 9, 2017 03:46, "Tom Lane" <tgl@sss.pgh.pa.us wrote:
     Well, it's imprecise.  Most people don't like that when it comes to
     monetary amounts.

 Could try using NUMERIC datatype for such a field. 

That worked.  I have set the column type to NUMERIC(10, 2) and it seemed to have worked fine.  I am not dealing with large amounts here, so 10 digits is plenty.

This is a "staging" phase where I first import the data into Postgres and then I will move it into the permanent tables in the next phase, so even taking it as VARHCAR would have been OK.  I just worried about using FLOAT/DOUBLE, and Tom confirmed that that was the wrong way to go.

Thanks again,

Igal Sapir
Lucee Core Developer
Lucee.org

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
Allan Kamau
Date:


On Nov 9, 2017 04:12, "Igal @ Lucee.org" <igal@lucee.org> wrote:
Thank you all for your help:


On 11/8/2017 4:45 PM, Tom Lane wrote:
"Igal @ Lucee.org" <igal@lucee.org> writes:
The value in the offending insert is:  0.0

Why does Postgres decide that 0.0 is "double precision" (which is a 
weird name in my opinion -- why can't it just be double) and not money?
Kettle must be telling it that --- on its own, PG would think '0.0'
is numeric, which it does have a cast to money for.

Looks like you are correct.  Kettle shows me the INSERT statement and when I execute it outside of Kettle (in a regular SQL client), the INSERT succeeds.

On 11/8/2017 4:45 PM, David G. Johnston wrote:
The lack of quotes surrounding the value is significant.  Money input requires a string literal.  Only (more or less) integer and double literal values can be written without the single quotes.

That didn't work.  I CAST'ed the value in the SELECT to VARCHAR(16) but all it did was change the error message to say that it expected `money` but received `character varying`.

On 11/8/2017 4:52 PM, Allan Kamau wrote:
 On Nov 9, 2017 03:46, "Tom Lane" <tgl@sss.pgh.pa.us wrote:
     Well, it's imprecise.  Most people don't like that when it comes to
     monetary amounts.

 Could try using NUMERIC datatype for such a field. 

That worked.  I have set the column type to NUMERIC(10, 2) and it seemed to have worked fine.  I am not dealing with large amounts here, so 10 digits is plenty.

This is a "staging" phase where I first import the data into Postgres and then I will move it into the permanent tables in the next phase, so even taking it as VARHCAR would have been OK.  I just worried about using FLOAT/DOUBLE, and Tom confirmed that that was the wrong way to go.

Thanks again,

Igal Sapir
Lucee Core Developer
Lucee.org

Maybe using NUMERIC without explicitly stating the precision is recommended. This would allow for values with many decimal places to be accepted without truncation. Your field may need to capture very small values such as those in bitcoin trading or some banking fee or interest.

Allan. 

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
"Igal @ Lucee.org"
Date:
On 11/8/2017 5:27 PM, Allan Kamau wrote:
Maybe using NUMERIC without explicitly stating the precision is recommended. This would allow for values with many decimal places to be accepted without truncation. Your field may need to capture very small values such as those in bitcoin trading or some banking fee or interest.

That's a very good idea.  For some reason I thought that I tried that earlier and it didn't work as expected, but I just tested it (again?) and it seems to work well, so that's what I'll do.

Thank you,

Igal Sapir
Lucee Core Developer
Lucee.org

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
"Igal @ Lucee.org"
Date:
On 11/8/2017 6:25 PM, Igal @ Lucee.org wrote:
> On 11/8/2017 5:27 PM, Allan Kamau wrote:
>> Maybe using NUMERIC without explicitly stating the precision is 
>> recommended. This would allow for values with many decimal places to 
>> be accepted without truncation. Your field may need to capture very 
>> small values such as those in bitcoin trading or some banking fee or 
>> interest.
>
> That's a very good idea.  For some reason I thought that I tried that 
> earlier and it didn't work as expected, but I just tested it (again?) 
> and it seems to work well, so that's what I'll do.

Another weird thing that I noticed:

On another column, "total_charged", that was migrated properly as a 
`money` type, when I run `sum(total_charged::money)` I get `null`, but 
if I cast it to numeric, i.e. `sum(total_charged::numeric)`, I get the 
expected sum result.

Is there a logical explanation to that?


Igal


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
Allan Kamau
Date:


On Thu, Nov 9, 2017 at 9:58 AM, Igal @ Lucee.org <igal@lucee.org> wrote:
On 11/8/2017 6:25 PM, Igal @ Lucee.org wrote:
On 11/8/2017 5:27 PM, Allan Kamau wrote:
Maybe using NUMERIC without explicitly stating the precision is recommended. This would allow for values with many decimal places to be accepted without truncation. Your field may need to capture very small values such as those in bitcoin trading or some banking fee or interest.

That's a very good idea.  For some reason I thought that I tried that earlier and it didn't work as expected, but I just tested it (again?) and it seems to work well, so that's what I'll do.

Another weird thing that I noticed:

On another column, "total_charged", that was migrated properly as a `money` type, when I run `sum(total_charged::money)` I get `null`, but if I cast it to numeric, i.e. `sum(total_charged::numeric)`, I get the expected sum result.

Is there a logical explanation to that?


Igal



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Since you are migrating data into a staging table in PostgreSQL, you may set the field data type as TEXT for each field where you have noticed or anticipate issues.
Then after population perform the datatype transformation query on the given fields to determine the actual field value that could not be gracefully transformed.
For example
SELECT a.* FROM <staging_schema>.<staging_table> a WHERE a.<field_that_should_contain_money_values>::NUMERIC IS NULL LIMIT 10;


or to identify values not within the expected range, substitute the place holders in the query below with appropriate values and issue the query.

SELECT a.* FROM <staging_schema>.<staging_table> a WHERE NOT a.<field_that_should_contain_money_values>::NUMERIC BETWEEN <expected_lowerbound_value> AND <expected_upperbound_value> LIMIT 10;


Once you have determined the issues and solved them. Construct a second table having similar field names but more restrictive (correct) data types such as NUMERIC where appropriate. The insert into this table the data from the staging table. Your insertion query would have the data casting clauses.


Allan.








Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
Adam Brusselback
Date:
> Since you are migrating data into a staging table in PostgreSQL, you may set
> the field data type as TEXT for each field where you have noticed or
> anticipate issues.
> Then after population perform the datatype transformation query on the given
> fields to determine the actual field value that could not be gracefully
> transformed.

This is the approach I have come to as the most successful for data migrations.

I will use tools like Kettle / Talend to get data into a staging table
with every column as text, then use SQL to migrate that to a properly
typed table.  Works much better than trying to work within the
constraints of these tools.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
Merlin Moncure
Date:
On Thu, Nov 9, 2017 at 8:22 AM, Adam Brusselback
<adambrusselback@gmail.com> wrote:
>> Since you are migrating data into a staging table in PostgreSQL, you may set
>> the field data type as TEXT for each field where you have noticed or
>> anticipate issues.
>> Then after population perform the datatype transformation query on the given
>> fields to determine the actual field value that could not be gracefully
>> transformed.
>
> This is the approach I have come to as the most successful for data migrations.
>
> I will use tools like Kettle / Talend to get data into a staging table
> with every column as text, then use SQL to migrate that to a properly
> typed table.  Works much better than trying to work within the
> constraints of these tools.

YES

I call the approach 'ELT', (Extract, Load, Trasform).  You are much
better off writing transformations in SQL than inside of an ETL tool.
This is a perfect example of why.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

From
"Igal @ Lucee.org"
Date:
On 11/9/2017 8:19 AM, Merlin Moncure wrote:
On Thu, Nov 9, 2017 at 8:22 AM, Adam Brusselback
<adambrusselback@gmail.com> wrote:
Since you are migrating data into a staging table in PostgreSQL, you may set
the field data type as TEXT for each field where you have noticed or
anticipate issues.
Then after population perform the datatype transformation query on the given
fields to determine the actual field value that could not be gracefully
transformed.
This is the approach I have come to as the most successful for data migrations.

I will use tools like Kettle / Talend to get data into a staging table
with every column as text, then use SQL to migrate that to a properly
typed table.  Works much better than trying to work within the
constraints of these tools.
YES

I call the approach 'ELT', (Extract, Load, Trasform).  You are much
better off writing transformations in SQL than inside of an ETL tool.
This is a perfect example of why.

All sound advice.  Thanks.


Igal Sapir

Lucee Core Developer
Lucee.org