postgresql - UPSERT 测试代码中的语法错误

Question

我正在尝试测试新的 PostgreSQL upsert syntax使用以下测试代码，但出现语法错误:

test=> CREATE TABLE test1 (
test(>         key1 integer PRIMARY KEY check (key1 > 0),
test(>         key2 integer check (key2 > 0)
test(> );
CREATE TABLE

test=> CREATE OR REPLACE FUNCTION upsert(IN in_json_array jsonb)
test->         RETURNS void AS
test-> $func$
test$>         UPDATE test1 t SET     
test$>         t.key1 = (obj->>'key1')::int,
test$>         t.key2 = (obj->>'key2')::int
test$>         FROM JSONB_ARRAY_ELEMENTS(in_json_array) obj
test$>         WHERE  t.key1 = obj->'key1'
test$>         ON CONFLICT DO UPDATE SET
test$>         key1 = excluded.key1,
test$>         key2 = excluded.key2;
test$> 
test$> $func$ LANGUAGE sql;

ERROR:  syntax error at or near "ON"
LINE 9:         ON CONFLICT DO UPDATE SET
                ^

请问为什么上面的代码会失败？

此外，test1 表有几个约束条件(非负值和唯一主键)。如何解决唯一性约束？

UPDATE 2:我已从 UPDATE 切换到 INSERT(很抱歉犯了这个愚蠢的错误!)，但我仍在为语法问题苦苦挣扎:

test=> CREATE OR REPLACE FUNCTION upsert(IN in_json_array jsonb)
test->         RETURNS void AS
test-> $func$
test$>         INSERT into test1 AS t (t.key1, t.key2)
test$>         VALUES ((obj->>'key1')::int, (obj->>'key2')::int)
test$>         FROM JSONB_ARRAY_ELEMENTS(in_json_array) obj
test$>         WHERE t.key1 = obj->'key1'
test$>         ON CONFLICT DO UPDATE SET
test$>         t.key1 = excluded.key1,
test$>         t.key2 = excluded.key2;
test$> $func$ LANGUAGE sql;

ERROR:  syntax error at or near "FROM"
LINE 6:         FROM JSONB_ARRAY_ELEMENTS(in_json_array) obj
                ^

我还尝试将 JSON 行更改为:

SELECT obj FROM JSONB_ARRAY_ELEMENTS(in_json_array)

但这也失败了......

为方便起见，这是我的测试代码:

select upsert('[{"key1":1,"key2":2},{"key1":3,"key2":4}]'::jsonb);
select upsert('[{"key1":1,"key2":2},{"key1":1,"key2":4}]'::jsonb);

Answer 1

因为这是错误率最高的 Google 结果:

ON CONFLICT DO UPDATE command cannot affect row a second time

我会补充说这可能是由重复冲突值引起的，例如

INSERT INTO distributors (did, dname)
VALUES 
    (5, 'Gizmo Transglobal'), 
    (5, 'Associated Computing, Inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

在这种情况下，我们尝试插入两个 dim 设置为 5 的值。由于 dim 是索引，因此它在查询本身中不能有冲突。

我在实现微服务和处理请求时遇到了这个错误，其中有一些有重复的记录。