Re: Migrating money column from MS SQL Server to Postgres

On Nov 9, 2017 04:12, "Igal @ Lucee.org" <igal(at)lucee(dot)org> wrote:

Thank you all for your help:

On 11/8/2017 4:45 PM, Tom Lane wrote:

"Igal @ Lucee.org" <igal(at)lucee(dot)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/>

Maybe using NUMERIC without explicitly stating the precision is
recommended. This would allow for values with many decimal places to be
accepted without truncation. Your field may need to capture very small
values such as those in bitcoin trading or some banking fee or interest.

Allan.