Re: 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(at)lucee(dot)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(at)sss(dot)pgh(dot)pa(dot)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 <http://lucee.org/>