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

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