0

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?

  • That's just a primary key collision. Try again with an id that doesn't yet exist in the table. – Laurenz Albe Oct 18 at 18:29
  • this is migration tool so I don't have control over changing inserts.. looking for a way to find the collision – Mike Oct 18 at 18:38
  • The error message tells you that the constraint "index_brokered_carriers_on_account_id_and_external_id" is violated, so all you need to do is find rows with duplicate values in those two columns. – mustaccio Oct 18 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.