From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Igal (at) Lucee(dot)org" <igal(at)lucee(dot)org> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Migrating money column from MS SQL Server to Postgres |
Date: | 2017-11-09 00:45:01 |
Message-ID: | 4497.1510188301@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2017-11-09 00:45:03 | Re: Migrating money column from MS SQL Server to Postgres |
Previous Message | Igal @ Lucee.org | 2017-11-09 00:32:56 | Migrating money column from MS SQL Server to Postgres |