PostgreSQL根据查询结果执行插入或更新操作

最近需要把老项目中Oracle语法改为pg的，其中一个逻辑为导入Excel数据，根据数据的唯一标识做插入或者更新操作，数据库中有此数据，则执行更新，反之插入。

Oracle代码：

	declare
    v_count    number;
    
    begin
    	select count(1) into v_count from uns_author_manage a where a.author_no = #AUTHOR_NO#;
		<![CDATA[
		if v_count = 0 then
			insert into uns_author_manage
			  (id,
			   author_no,
			   author_type,
			   project_name,
			   project_property,
			   application_com,
			   valid,
			   bid_invitation,
			   estimate_coverage,
			   estimate_ppremium,
			   author_person_name,
			   author_pseson_job,
			   operator,
			   operator_name,
			   create_date
			   )
			   
			values(
			   seq_uns_author_manage.nextval
			   ,nvl(#AUTHOR_NO#,'')
			   ,nvl(#AUTHOR_TYPE#,'')
			   ,nvl(#PROJECT_NAME#,'')
			   ,nvl(#PROJECT_PROPERTY#,'')
			   ,nvl(#APPLICATION_COM#,'')
			   ,nvl(#VALID#,'')
			   ,nvl(#BID_INVITATION#,'')
			   ,nvl(#ESTIMATE_COVERAGE#,'')
			   ,nvl(#ESTIMATE_PPREMIUM#,'')
			   ,nvl(#AUTHOR_PERSON_NAME#,'')
			   ,nvl(#AUTHOR_PSESON_JOB#,'')
			   ,nvl(#USERID#,'')
			   ,nvl(#OPERATOR_NAME#,'')
			   ,sysdate);
		else
			update uns_author_manage
			   set author_type = nvl(#AUTHOR_TYPE#,'')
				   ,project_name = nvl(#PROJECT_NAME#,'')
				   ,project_property = nvl(#PROJECT_PROPERTY#,'')
				   ,application_com = nvl(#APPLICATION_COM#,'')
				   ,valid = nvl(#VALID#,'')
				   ,bid_invitation = nvl(#BID_INVITATION#,'')
				   ,estimate_coverage = nvl(#ESTIMATE_COVERAGE#,'')
				   ,estimate_ppremium = nvl(#ESTIMATE_PPREMIUM#,'')
				   ,author_person_name = nvl(#AUTHOR_PERSON_NAME#,'')
				   ,author_pseson_job = nvl(#AUTHOR_PSESON_JOB#,'')
				   ,operator = nvl(#USERID#,'')
				   ,operator_name = nvl(#OPERATOR_NAME#,'')
				   ,update_date = sysdate
			   where author_no = #AUTHOR_NO#;
		end if;
		]]>
	end;

改为pg代码：

			insert into uns_author_manage
			  (
			   author_no,
			   author_type,
			   project_name,
			   project_property,
			   application_com,
			   valid,
			   bid_invitation,
			   estimate_coverage,
			   estimate_ppremium,
			   author_person_name,
			   author_pseson_job,
			   operator,
			   operator_name,
			   create_date
			   )
			values(
				coalesce(#AUTHOR_NO#)
			   ,coalesce(#AUTHOR_TYPE#)
			   ,coalesce(#PROJECT_NAME#)
			   ,coalesce(#PROJECT_PROPERTY#)
			   ,coalesce(#APPLICATION_COM#)
			   ,coalesce(#VALID#)
			   ,coalesce(#BID_INVITATION#)
			   ,coalesce(#ESTIMATE_COVERAGE#)
			   ,coalesce(#ESTIMATE_PPREMIUM#)
			   ,coalesce(#AUTHOR_PERSON_NAME#)
			   ,coalesce(#AUTHOR_PSESON_JOB#)
			   ,coalesce(#USERID#)
			   ,coalesce(#OPERATOR_NAME#)
			   ,now())
				 ON conflict(author_no)
				 DO update set 
			      author_type = coalesce(#AUTHOR_TYPE#)
				   ,project_name = coalesce(#PROJECT_NAME#)
				   ,project_property = coalesce(#PROJECT_PROPERTY#)
				   ,application_com = coalesce(#APPLICATION_COM#)
				   ,valid = coalesce(#VALID#)
				   ,bid_invitation = coalesce(#BID_INVITATION#)
				   ,estimate_coverage = coalesce(#ESTIMATE_COVERAGE#)
				   ,estimate_ppremium = coalesce(#ESTIMATE_PPREMIUM#)
				   ,author_person_name = coalesce(#AUTHOR_PERSON_NAME#)
				   ,author_pseson_job = coalesce(#AUTHOR_PSESON_JOB#)
				   ,operator = coalesce(#USERID#)
				   ,operator_name = coalesce(#OPERATOR_NAME#)
				   ,update_date = now();

不过要注意下，被conflict()函数修饰的键要具有唯一索引，否则会报错，使用以下代码创建唯一索引：

alter table uns_author_manage add constraint id_cons unique(author_no);		

本文地址：https://blog.csdn.net/xianyun1992/article/details/108997144