Datatype mismatch in data migration

[pradnyajadhav1]

Hi All,

I have been trying to load source data which is in sql server to AWS RDS Postgres Database. 

Qlik is able to migrate successfully. However, field having datatype "Timestamp" in SQL server getting change to varchar(37) in Postgres.

Any solution to fix this issue? 

[sureshkumar]

Hello @pradnyajadhav1 

As per user guide, it should be "BYTEA" not sure why the datatype has been changed.

Kindly let us know how the steps done for migration process.

Regards,

Suresh

[pradnyajadhav1]

Thanks for your response.

So, I have created a task where I have configured sql server as source and postgres as target. 

Then, I selected source  table in table selection. I specified target schema and target table name in table settings and kept transformation as default [ column to column transformation - no additional transformation applied ] 

In transformation section of table settings, I see DATETIME datatype for target column correctly. 

then I ran the task. It loaded all the data from source to target. Data looks correct. But, Data type of datetime column is loading as a Varchar in target. 

That is the steps I have taken for migration.

[sureshkumar]

Hello @pradnyajadhav1 

As mentioned in the user guide,

DATETIME

If scale is => 0 and =< 6, then:

TIMESTAMP

If scale is => 7 and =< 12, then:

VARCHAR (37)

PostgreSQL Database target data types #PostgreSQL Database target data types | Qlik Replicate Help

Regards,
Suresh

[pradnyajadhav1]

Thank you Suresh! 
Let me check on this from my end. 

[pradnyajadhav1]

Hi @sureshkumar ,

I verified source data and type and yes, scale is 7 which is why Qlik is loading column with datatype varchar(37).

Thank you for your assisting on this. 

However, I cannot change this type in my source so I need to handle this in my target looks like. 
So, if I create a table with all appropriate and expected table structure as source in my target first , Will that affect anything at the time of initial load via Qlik or any possible data loss? 

[sureshkumar]

Hello @pradnyajadhav1 
Once you create the table in the target side same as source structure, you need go with truncate before loading in Full load settings as it has to update metadata.

Once you've done the Full Load, Task will drop the existing data and load the new metadata and data as per the settings.

Kindly test it in the lower environment/Test task before applying in Existing Task.

Regards,
Suresh