【故障处理】 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,如果你有问题,欢迎给我留言。