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`.
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.
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.
