sql - Knex on constraint upsert 冲突

Question

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

我没有在文档中找到如何使用唯一约束来打乱行。

给定以下约束:

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

我尝试使用以下代码执行更新插入:

 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')

产生

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

要成功操作，输出必须是on conflict on constraint。我该如何解决？

Answer 1

您可以指定构成唯一约束的确切字段名称。

 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')