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

[john_wang]

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:

1. Open PostgreSQL source endpoint
2. Go to the Advanced tab
3. Open Internal Parameters
4. Add a new parameter named additionalConnectionProperties
   
5. Press <Enter> and set the parameter's value to:
   
   D6=12;
   
   
   
   
6. 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.
   
   
   
7. Reload the task

Related Content

PostgreSQL ODBC Driver treats data type Numeric(without precision) AS as "default" by default, change it to "varchar" then the NUMERIC will be parsed as STRING(1000). Replicate applies the implicit data type mapping from STRING(1000) to custom precision and scale.

        The parameter name in ODBC file is "D6", it's values are:

• Default : -101
• varchar : 12
• double  : 8
• memo    : -1

        In ODBC Manager it's:

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  

[sravyakolli]

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

[sravyakolli]

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

[john_wang]

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:

1. Open PostgreSQL source endpoint
2. Go to the Advanced tab
3. Open Internal Parameters
4. Add a new parameter named additionalConnectionProperties
   
5. Press <Enter> and set the parameter's value to:
   
   D6=12;
   

Let me know if you need any additional assistance.

Regards,

John.

[SK2]

@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

[Sonja_Bauernfeind]

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