From: | "Igal (at) Lucee(dot)org" <igal(at)lucee(dot)org> |
---|---|
To: | Postgres General Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Migrating money column from MS SQL Server to Postgres |
Date: | 2017-11-09 01:11:33 |
Message-ID: | 4b6a0bfb-0cbc-b407-feaa-0be86f06e4d9@lucee.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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/>
From | Date | Subject | |
---|---|---|---|
Next Message | Allan Kamau | 2017-11-09 01:27:17 | Re: Migrating money column from MS SQL Server to Postgres |
Previous Message | Allan Kamau | 2017-11-09 00:52:45 | Re: Migrating money column from MS SQL Server to Postgres |