【故障处理】 PostgreSQL : ERROR: syntax error at or near “ON“ ON conflict语法

【故障处理】 PostgreSQL : ERROR: syntax error at or near “ON”

故障分析及解决过程

故障环境介绍

项目	source db
数据库类型	PostgreSQL
框架	springboot
问题范围	pg数据库无法支持 ON conflict语法

故障发生现象及报错信息

报错sql

with tmp_t0 as (
  select 'a'::varchar as c0 union all
  select 'b'::varchar as c0 union all
  select null as c0 
)

INSERT INTO  tmp_t0  (c0)
select *
  from tmp_t0 t0
 where 1=1
   and not exists ( select 1 
                      from tmp_t0 t1 
				     where 1=1
				       and t1.c0 = 'a' 
					   and t0.c0 = t1.c0 
			       )

ON conflict ( c0 ) DO
UPDATE 
	SET c0 = EXCLUDED.c0 RETURNING 1

故障分析及解决过程

通过各种查询相关资料,对比mysql,oracle的实现方式,最终采用的实现方式:

in、not in

in

with tmp_t0 as (
  select 'a'::varchar as c0 union all
  select 'b'::varchar as c0 union all
  select null as c0 
)
select *
  from tmp_t0 t0
 where 1=1
   and t0.c0 in ('a')
;

 c0 
----
 a
(1 row)

not in

with tmp_t0 as (
  select 'a'::varchar as c0 union all
  select 'b'::varchar as c0 union all
  select null as c0 
)
select *
  from tmp_t0 t0
 where 1=1
   and t0.c0 not in ('a')
;

 c0 
----
 b
(1 row)

exists、not exists

exists

with tmp_t0 as (
  select 'a'::varchar as c0 union all
  select 'b'::varchar as c0 union all
  select null as c0 
)
select *
  from tmp_t0 t0
 where 1=1
   and exists ( select 1 
                  from tmp_t0 t1 
				 where 1=1
				   and t1.c0 = 'a' 
				   and t0.c0 = t1.c0  
			   )
;

 c0 
----
 a
(1 row)

not exists

with tmp_t0 as (
  select 'a'::varchar as c0 union all
  select 'b'::varchar as c0 union all
  select null as c0 
)
select *
  from tmp_t0 t0
 where 1=1
   and not exists ( select 1 
                      from tmp_t0 t1 
				     where 1=1
				       and t1.c0 = 'a' 
					   and t0.c0 = t1.c0 
			       )
;

 c0 
----
 b
 
(2 rows)

总结

可以先更新,然后再插入;

update pg_test.test_on_conflict_01  set update_time = temp.update_time
from (

select update_time  from  pg_test.test_on_conflict_02  toc2 where  toc2.create_time  ='20201009'

) temp 
where test_on_conflict_01.key_id = temp.key_id ;

insert into pg_test.test_on_conflict_01(key_id,update_time)
select * from
(
select key_id, update_time  from  pg_test.test_on_conflict_02  toc2 where  toc2.create_time  ='20201009'
)
) toc_all
	where not  exists (
		select * from  pg_test.test_on_conflict_01 
		where fee_overdue_rate_month.key_id = toc_all.key_id 
	);

参考链接:
postgresql insert and update 插入更新.
in、not in、exists、not exists 在postgresql中的处理.

life is beautiful，我是Alon，如果你有问题，欢迎给我留言。