Re: Migrating money column from MS SQL Server to Postgres

On Nov 9, 2017 03:46, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

"Igal @ Lucee.org" <igal(at)lucee(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Could try using NUMERIC datatype for such a field.

Allan