Replicate failing to load target table with new identity column (GENERATED BY DEFAULT)

[Jimbo91]

I'm trying to migrate data from Oracle to AWS Aurora Postgres.  I have a table which has a compound PK on the oracle source, but there is a new PK identity column on the new target table.  In the Table Settings, in Transformations, I have mapped all of the columns, and initially did not include the new identity column in the target list of columns.  This failed to load, due to the target column being NULL.  So I tried adding the new column in the Transformations, this also failed with the same error.  I've also tried setting a function to try and do a target lookup, and get the max value of the new column on the target table and + 1.  The load task just runs and never does anything.  What is the correct way to load this data to the target table and have the new column generate new values?  

[Heinvandenheuvel]

Hmm, how about you do NOT mention the new identity column at all on the Replicate side and have it provide a default fresh ID value on target. That should work for load, but not updates.

Is there a PK on the source? You could probably still propagate that but as a Unique Key on target, allowing updates/deletes but with reduced performance. Not sure though... Try it? 

Hein.

[DesmondWOO]

Hi @Jimbo91 ,

In your PostgreSQL endpoint --> Advanced --> Internal Parameters, find this parameter "loadUsingCSV" and uncheck it.

Hope this helps.

Regards,
Desmond

[Jimbo91]

Hein, the Oracle table has 2 columns as PK, the target Postgres table has new single identity column (transmissions_id) as the PK with the original 2 columns as a Unique constraint. 

I removed the new identity column from the Qlik Replicate task transformation for this table completely.

I still fail with the following 'ERROR: null value in column "transmissions_id" of relation "transmissions" violates not-null constraint'.

For some reason, when Qlik tries to load the data, it does not have a value for the transmissions_id so it is setting to null, but when it goes to Postgress, I expect it to generate a value for the identity column, but it is not, and instead failing because of the NOT NULL setting on the identity column.

[john_wang]

Hello @Jimbo91 ,

By default Qlik Replicate utilizes the LOAD program to upload the interim .CSV files to target side and then COPY the data from .CSV  files to final target table. With this mode the performance is the best.

However please take note the .CSV  files contains the source table columns data; and the LOAD utility get the target side table metadata as there is an additional column which data type is SERIAL in target side, the metadata is not match between the .CSV  file and target table, so the SERIAL column's value will be put as NULL  and leads the problem. This is how the LOAD utility works.

AS @DesmondWOO mentioned, set loadUsingCSV to FALSE will let Replicate to write the data by SQL INSERT statement rather than LOAD & COPY , so far the PostgreSQL database will maintain the auto-increasing values for the SERIAL columns values. Turn off LOAD utility will solve the problem, Certainly, the INSERT method performance is not as good as LOAD method.

Hope this helps.

John.

[john_wang]

Hello @Jimbo91 ,

Besides turn off loadUsingCSV option, another option is that keep using LOAD method, and manually control the task running (eg if the performance is critical for the project):

1- Do not turn set loadUsingCSV option 

2- Do not add SERIAL column in target table, but use the default one to one columns map (source table and target table columns are identical)

3- In Task setting, Stop the task after Full Load done (Before or After cached changed have been applied, see below sample). Startup the task and wait for the Full Load finish. The task will stop automatically.

4- After the task stopping, Manually add the SERIAL column in target side table, in my sample the column name is autoinc :

     alter table scott.autoinc add autoinc SERIAL;

     PostgreSQL database will assign values to the column autoinc automatically.

5- Resume the task. Then the task CDC stage will continue to run.

Hope this helps.

John.

[SushilKumar]

Hello team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar