Re: Migrating money column from MS SQL Server to Postgres

Allan Kamau <kamauallan@xxxxxxxxx> · Thu, 9 Nov 2017 04:27:17 +0300

On Nov 9, 2017 04:12, "Igal @ Lucee.org" <igal@xxxxxxxxx> wrote:

    Thank you all for your help:

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

      "Igal @ Lucee.org" <igal@xxxxxxxxx> 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@xxxxxxxxxxxxx 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

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. 