UPSERT测试代码中的语法错误 [英] Syntax error in UPSERT test code

问题描述

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

I am trying to test the new PostgreSQL upsert syntax with the following test code, but get the syntax error:

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
                ^

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

Why does the above code fail please?

此外， test1 表具有多个约束（非负值和唯一主键）。如何仅解决唯一性约束？

Also, the test1 table has several constraints (non-negative values and the unique primary key). How to address the uniqueness constraint only?

更新2：我已从 UPDATE 插入 INSERT （很抱歉犯了一个愚蠢的错误！），但仍在语法上苦苦挣扎：

UPDATE 2: I have switched from UPDATE to INSERT (sorry for the silly mistake!), but am still struggling with the syntax:

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行更改为：

I have also tried to change the JSON-line to:

SELECT obj FROM JSONB_ARRAY_ELEMENTS(in_json_array)

但这也失败了...

这是我的测试代码，为您提供方便：

Here is my test code for your convenience:

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

推荐答案

如果您使用的是 from 子句中，您不能使用 values 。 插入..值（..） 或 插入..选择...从... 但不能同时使用两者。

If you are using a from clause you can't use values. It's either insert into .. values (..) or insert into .. select ... from ... but not both.

您也无法从 select 子句。我不确定您要达到的目标。

You also can't reference the target table from the insert in the select clause that feeds the insert. I am not sure what you are trying to achieve with that.

您还需要通过指定PK列或应处理的约束的名称来确定应捕获哪个冲突。在您的情况下，它应该是pk列：

You also need to qualify which "conflict" should be trapped by specifying the PK columns or the name of the constraint that should be handled. In your case it should be the pk column:

所有内容放在一起，函数应如下所示：

All that put together, the function should look like this:

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

在（）上的与众不同选择将只返回key1的一个值，以避免出现 ON CONFLICT DO UPDATE命令不能再次影响行错误。请注意，在选择中没有 的顺序，基本上是随机选择了哪个键。如果要选择特定订单，则需要在 select

The distinct on () will make sure that only one value for key1 will be returned by the select to avoid the "ON CONFLICT DO UPDATE command cannot affect row a second time" error. Note that without an order by in the select it is basically "random" which key is being chosen. If you want to pick a specific one, you need to add an order by to the select

这篇关于UPSERT测试代码中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！