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,