Postgresql实现merge into功能

Oracle中merge into功能转移到Postgresql数据库

一、异常1
SQL 错误[42P10]: ERROR: Join column of “task_name” must be distribute key in MERGE INTO

错误原因：Postgresql中要求merge into语句中的关联字段必须为主键

二、异常2
SQL 错误[42P10]: ERROR: there is no unique or exclusion constraion matching the oN CONFLCT specification
Error position :

错误原因：Postgresql中要求merge into语句中的关联字段必须在表中字段顺序的第一个位置

三、Postgresql中使用insert into xxx on conflict() do update set语法来代替oracle中的merge into操作

insert into table_1(column_1,column_2, column_3)
select column_1,
       column_2,
       column_3,
  from table_2
    on conflict (column_1)
	do update set
    column_2 = excluded.column_2,
	column_3 = excluded.column_3

如果要保留原表数据不需要更新，则使用 do nothing，如

insert into table_1(column_1,column_2, column_3)
select column_1,
       column_2,
       column_3
  from table_2
    on conflict (column_1)
	do nothing

需要注意的是：
1、conflict 字段必须是主键
2、select出来的字段名称必须和目标表中的字段名称一致，不然会报如下异常：
SQL 错误 [42703]: ERROR: column excluded.column_xxx does not exist
Position: 3693

insert into table_1(column_1,column_2, column_3)
select column_1,
       column_xxx as column_2,
       column_3
  from table_2
    on conflict (column_1)
	do update set
    column_2 = excluded.column_2,
	column_3 = excluded.column_3