article Qlik Replicate: How to keep NUMERIC field precision and scale for PostgreSQL source endpoint in Official Support Articles

Qlik Replicate: How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

john_wang — Tue, 07 Nov 2023 08:13:56 GMT

When the source endpoint PostgreSQL Data Type is NUMERIC (without precision/scale), it is map to NUMBER(28,6) , the decimal point maybe truncated. For example the source value 0.1234567890123456 will be truncated to 0.123456 in target endpoint. This is Replicate and PostgreSQL default behavior, the data type mapping is in Replicate User Guide PostgreSQL Data Types.

This article describes how to overcome the limitation and keep NUMERIC field precision and scale.

Resolution

Add an internal parameter to adjust PostgreSQL behavior:

Open PostgreSQL source endpoint
Go to the Advanced tab
Open Internal Parameters
Add a new parameter named additionalConnectionProperties
Press <Enter> and set the parameter's value to:

D6=12;
Change the Type in table transformation, from STRING(1000) to NUMERIC(28,16), or any other precision/scale you want. In the below sample, the column "nm" data type is NUMERIC.
Reload the task

Environment

Qlik Replicate all versions
PostgreSQL all Server versions (psqlODBC 11.00.0000 Release and above)

Internal Investigation ID(s):

Support cases, #00069371

Feature Request Add precision and scale for Postgresql source endpoint for numeric fields

Qlik Replicate

Re: Replicate - How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

sravyakolli — Fri, 03 Nov 2023 12:28:11 GMT

@john_wang - Great blog. one question - How about the NUMERIC fields having Integer values. Those are appearing as (example: 123.000000). pls advise how to fix it.

Re: Replicate - How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

sravyakolli — Fri, 03 Nov 2023 12:34:28 GMT

@john_wang - Also how to fix it for Replicate on Linux. looks like above screenshot is for Windows ODBC driver.

Re: Replicate - How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

john_wang — Fri, 03 Nov 2023 14:53:44 GMT

Hello @sravyakolli ,

Thank you for the update.

@john_wang - Great blog. one question - How about the NUMERIC fields having Integer values. Those are appearing as (example: 123.000000). pls advise how to fix it.

If I understood correctly, you could change the data type in the above step (6), from STRING(1000) to INT, eg INT4.

@john_wang - Also how to fix it for Replicate on Linux. looks like above screenshot is for Windows ODBC driver.

The Windows ODBC Manager was used to show the parameter name and parameter values meaning.

The resolution is same while Replicate running on Linux:

Open PostgreSQL source endpoint
Go to the Advanced tab
Open Internal Parameters
Add a new parameter named additionalConnectionProperties
Press <Enter> and set the parameter's value to:

D6=12;

Let me know if you need any additional assistance.

Regards,

John.

Re: Replicate - How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

SK2 — Tue, 07 Nov 2023 06:41:04 GMT

@john_wang - Thanks for your response. We have NUMERIC datatype for most of the tables and they have mix of data. Here is the expected translation. Please let me know how can we achieve this? having to go through each field would be tedious.

123 -> 123

123.2 - > 123.2

123.653 - > 123.653

123.4567242424242 - > 123.4567242424242

Re: Replicate - How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

Sonja_Bauernfeind — Tue, 07 Nov 2023 08:05:54 GMT

Hello @SK2

To make certain that your question has the right reach (our community as well as our active agents), please post your query in our Qlik Replicate forum.

All the best,
Sonja

article Qlik Replicate: How to keep NUMERIC field precision and scale for PostgreSQL source endpoint in Official Support Articles

Qlik Replicate: How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

Resolution

Related Content

Environment

Internal Investigation ID(s):

Re: Replicate - How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

Re: Replicate - How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

Re: Replicate - How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

Re: Replicate - How to keep NUMERIC field precision and scale for PostgreSQL source endpoint

Re: Replicate - How to keep NUMERIC field precision and scale for PostgreSQL source endpoint