Re: Migrating money column from MS SQL Server to Postgres

On Thu, Nov 9, 2017 at 9:58 AM, Igal @ Lucee.org <igal(at)lucee(dot)org> wrote:

> On 11/8/2017 6:25 PM, Igal @ Lucee.org wrote:
>
>> On 11/8/2017 5:27 PM, Allan Kamau wrote:
>>
>>> 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.
>>>
>>
>> That's a very good idea. For some reason I thought that I tried that
>> earlier and it didn't work as expected, but I just tested it (again?) and
>> it seems to work well, so that's what I'll do.
>>
>
> Another weird thing that I noticed:
>
> On another column, "total_charged", that was migrated properly as a
> `money` type, when I run `sum(total_charged::money)` I get `null`, but if I
> cast it to numeric, i.e. `sum(total_charged::numeric)`, I get the expected
> sum result.
>
> Is there a logical explanation to that?
>
>
> Igal
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Since you are migrating data into a staging table in PostgreSQL, you may
set the field data type as TEXT for each field where you have noticed or
anticipate issues.
Then after population perform the datatype transformation query on the
given fields to determine the actual field value that could not be
gracefully transformed.
For example
SELECT a.* FROM <staging_schema>.<staging_table> a WHERE
a.<field_that_should_contain_money_values>::NUMERIC IS NULL LIMIT 10;

or to identify values not within the expected range, substitute the place
holders in the query below with appropriate values and issue the query.

SELECT a.* FROM <staging_schema>.<staging_table> a WHERE NOT
a.<field_that_should_contain_money_values>::NUMERIC BETWEEN
<expected_lowerbound_value> AND <expected_upperbound_value> LIMIT 10;

Once you have determined the issues and solved them. Construct a second
table having similar field names but more restrictive (correct) data types
such as NUMERIC where appropriate. The insert into this table the data from
the staging table. Your insertion query would have the data casting clauses.

Allan.