on conflict及ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

一、on conflict的使用

1.1、on conflict的使用场景

insert时遇到冲突时做一些操作

比如，我想插入一批数据，其中id字段是主键（唯一），如果id存在则更新数据，不存在则插入数据，就可以使用on conflict

1.2、on conflict的使用方式

• 方式一：on conflict on constraint unique_cbxxb do

  unique_cbxxb是唯一约束的名字，do后面放操作

  insert into my_table(user_id,
  		     name,
  		     card_num) 
  values(#{userId}, #{name}, #{cardNum})
  	on conflict on constraint unique_card_num do 
  	update my_table set card_num = #{cardNum} where user_id = #{userId}
  

• 方式二：on conflict (unique_columns) do

  unique_columns代表唯一约束的字段名

  insert into my_table(user_id,
  		     name,
  		     card_num) 
  values(#{userId}, #{name}, #{cardNum})
  	on conflict(card_num) do
  	update my_table set card_num = #{cardNum} where user_id = #{userId}
  

以上，do后面均可跟nothing, 代表：不违反唯一约束则插入，违反唯一约束则什么也不做

二、使用报错

在使用过程中有可能会报：ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification的错误

报这错的原因是没有给响应字段设置唯一约束
比如在如下sql中

insert into my_table(user_id,
		     name,
		     card_num) 
values(#{userId}, #{name}, #{cardNum})
	on conflict(card_num) do
	update my_table set card_num = #{cardNum} where user_id = #{userId}

如果card_num没有设置唯一约束，on conflict() 里面字段需要作为唯一键，否则有可能查到多行数据而无法更新，就会报该错误。