Search Postgresql Archives

Re: Migrating money column from MS SQL Server to Postgres

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Nov 9, 2017 03:46, "Tom Lane" <tgl@xxxxxxxxxxxxx> wrote:
"Igal @ Lucee.org" <igal@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Could try using NUMERIC datatype for such a field. 


Allan 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux