PG:Undefined object error when using activeimport with constraint

Question

I am trying to do an upsert with ActiveImport. When running the import I am trying to pass it a named constraint in order to tell it when to update certain columns.

 User.import(
      @import_values,
      on_duplicate_key_update: {
        constraint_name: :index_users_on_uid_and_tenant_id,
        columns: %i[title location_id email active]
      }
    )

The code above is my import statement which is correct according to activeimport documentation. However, when I run this, I get PG:UndefinedObject error which shows the following:

Caused by PG::UndefinedObject: ERROR:  constraint "index_users_on_uid_and_tenant_id" for table "users" does not exist

However, in my schema.rb, on my users table schema, the constraint clearly exists:

 t.index ["uid", "tenant_id"], name: "index_users_on_uid_and_tenant_id", unique: true

and also, this returns true:

ActiveRecord::Base.connection.index_exists?(:users, [:uid, :tenant_id]) => true

Any help as to why this is happening would be very helpful!

Answer 1

A unique constraint implies the creation of a unique index, but not vice versa.

In your case, you created a unique index but not a unique constraint, so there's no constraint "index_users_on_uid_and_tenant_id", you can add a constraint, or use conflict_target to declare explicitly which columns the conflict would occur

User.import(
      @import_values,
      on_duplicate_key_update: {
        conflict_target: [:uid, :tenant_id],
        columns: %i[title location_id email active]
      }
    )