I'm not sure how this happened to this table but have an issue with ERROR: duplicate key value violates unique constraint trying to use AWS's DMS service to move a postgres v10.1 DB to RDS v10
The table looks like
# \d+ public.brokered_carriers
Table "public.brokered_carriers"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------------+-----------------------------+-----------+----------+-----------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('brokered_carriers_id_seq'::regclass) | plain | |
name | character varying | | | | extended | |
code | character varying | | | | extended | |
external_id | character varying | | | | extended | |
mc_number | character varying | | | | extended | |
dot_number | character varying | | | | extended | |
phone_1 | character varying | | | | extended | |
phone_2 | character varying | | | | extended | |
tms_payment_address_id | integer | | | | plain | |
account_id | integer | | not null | | plain | |
data | jsonb | | | '{}'::jsonb | extended | |
created_at | timestamp without time zone | | | | plain | |
updated_at | timestamp without time zone | | | | plain | |
Indexes:
"brokered_carriers_pkey" PRIMARY KEY, btree (id)
"index_brokered_carriers_on_account_id_and_external_id" UNIQUE, btree (account_id, external_id)
Foreign-key constraints:
"fk_rails_39675fc9f0" FOREIGN KEY (account_id) REFERENCES accounts(id)
The DMS error is
dms-tasks-company dms-task-2JVAOJI7TQAZSPCVDXDTYBA5YE 2019-10-18T17:59:37 [TARGET_LOAD ]I: Table 'public'.'brokered_carriers' has Non-Optimized Full LOB Support (odbc_endpoint_imp.c:4708)
dms-tasks-company dms-task-2JVAOJI7TQAZSPCVDXDTYBA5YE 2019-10-18T17:59:37 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 23505 NativeError: 1 Message: ERROR: duplicate key value violates unique constraint "index_brokered_carriers_on_account_id_and_external_id"; Error while executing the query [1022510] (ar_odbc_stmt.c:2639)
dms-tasks-company dms-task-2JVAOJI7TQAZSPCVDXDTYBA5YE 2019-10-18T17:59:37 [SOURCE_UNLOAD ]I: Unload finished for table 'public'.'brokered_carriers' (Id = 18). 19163 rows sent. (streamcomponent.c:3473)
Any idea how to fix this and fix the constraint?
