Knex on conflict on constraint upsert

Question

https://knexjs.org/#Builder-onConflict

I haven't found in the documentation how to upset rows with unique constraints.

Given following constraint:

ALTER TABLE public.assignments_mappings ADD CONSTRAINT assignments_mappings_task_id_user_id_unique UNIQUE (task_id, user_id)

I trying to perform upsert with following code:

 return await db("assignments_mappings")
      .insert(
        tasksID.map(id => ({ task_id: id, user_id, company_id: 1 }))
      )
      .onConflict('assignments_mappings_task_id_user_id_unique')
      .merge()
      .returning('id')

Which generates

error: insert into "assignments_mappings" ("company_id", "task_id", "user_id") values ($1, $2, $3), ($4, $5, $6), ($7, $8, $9), ($10, $11, $12) on conflict ("assignments_mappings_task_id_user_id_unique") do update set "company_id" = excluded."company_id", "task_id" = excluded."task_id", "user_id" = excluded."user_id" returning "id" - column "assignments_mappings_task_id_user_id_unique" does not exist

For successful operation, the output gotta be on conflict on constraint. How can I solve it?

Answer 1

You could specify the exact field names that make up the unique constraint.

 return await db("assignments_mappings")
      .insert(
        tasksID.map(id => ({ task_id: id, user_id, company_id: 1 }))
      )
      .onConflict(['task_id', 'user_id'])
      .merge()
      .returning('id')