Overview

You recently migrated the Analytics database from Oracle to Postgresql. The nightly sync (ETL) job is failing with the below error:

• Task: DocumentUserETL : ERROR: duplicate key value violates unique constraint "jivedw_ou_pk" Detail: Key (object_id, object_type, user_id)=(xxxxx, yyyyy, zzzzz) already exists. Where: SQL statement "UPDATE jivedw_object_user SET object_id = v_stg.object_id, object_type = v_stg.object_type, user_id = v_stg.user_id WHERE object_id = v_stg.object_id AND object_type = v_stg.object_type" PL/pgSQL function jivedw_mergeobjectuser() line 8 at SQL statement

Solution

The logic to populate the jivedw_object_user table by the DocumentUserETL task is different for an Oracle database than for a Postgres database. 

Thus, to resolve this issue, perform the following steps in the Analytics database:

1. Take a backup of the jivedw_object_user table
2. Delete all records (truncate) the jivedw_object_user table
3. Run the ETL task again