代码之家 › 专栏 › 技术社区 › BarÄ±Å UÅaklÄ±

如何在ON冲突更新中使用CTE中的值

postgresql sql

BarÄ±Å UÅaklÄ± · 技术社区 · 1 年前

我有一张有两列的桌子 _key(string) 和 data(json) ,我正试图将数据追加到表中,并获得 error: missing FROM-clause entry for table "updates" 如果我删除DO UPDATE集并将其替换为DO NOTHING,错误就会消失。我也尝试过使用 EXLCLUDED.field & EXCLUDED.value 在UPDATE部分,但这会导致 error: column excluded.field does not exist

这是我试图修复的查询,如果存在冲突,查询应该更新json对象,以便将字段设置为oldValue+newValue。

WITH updates AS (
    SELECT UNNEST($1::TEXT[]) AS _key,
           UNNEST($2::TEXT[]) AS field,
           UNNEST($3::NUMERIC[]) AS value
)
INSERT INTO "legacy_hash" ("_key", "data")
SELECT _key, jsonb_build_object(field, value) FROM updates
ON CONFLICT ("_key")
DO UPDATE SET "data" = jsonb_set(
    "legacy_hash"."data", 
    ARRAY[updates.field], 
    to_jsonb(COALESCE(("legacy_hash"."data"->>updates.field)::NUMERIC, 0) + updates.value)
)

我有一个版本,适用于单一 (key, field, value) 在......下面

INSERT INTO "legacy_hash" ("_key", "data")
VALUES ($1::TEXT, jsonb_build_object($2::TEXT, $3::NUMERIC))
ON CONFLICT ("_key")
DO UPDATE SET "data" = jsonb_set(
    "legacy_hash"."data", 
    ARRAY[$2::TEXT], 
    to_jsonb(COALESCE(("legacy_hash"."data"->>$2::TEXT)::NUMERIC, 0) + $3::NUMERIC)
)

2 回复 | 直到 1 年前

Charlieface 1 年前

不幸的是, the only tables 您可以访问 ON CONFLICT DO 子句是当前行值(如表名或别名(如果有的话))和建议但失败的行(如 excluded 桌子)。

这个 SET 和 WHERE 条款 ON CONFLICT DO UPDATE 可以使用表的名称(或别名)访问现有行,并使用特殊 排除 桌子。

但就你的情况而言,你可以通过分解来以更复杂的方式重建你需要的东西 jsonb 让你的价值观得到体现。您可以使用JSONPath函数 keyvalue() 以JSONB数组的形式获取对象的键和值。

WITH updates AS (
    SELECT UNNEST($1::TEXT[]) AS _key,
           UNNEST($2::TEXT[]) AS field,
           UNNEST($3::NUMERIC[]) AS value
)
INSERT INTO legacy_hash (_key, data)
SELECT _key, jsonb_build_object(field, value)
FROM updates
ON CONFLICT (_key)
DO UPDATE SET data = jsonb_set(
    legacy_hash.data, 
    ARRAY[jsonb_path_query_first(excluded.data, '$.keyvalue()[0]')->>'key'],
    to_jsonb(
      COALESCE((legacy_hash.data->>(jsonb_path_query_first(excluded.data, '$.keyvalue()[0]')->>'key'::text))::NUMERIC, 0)
      + (jsonb_path_query_first(excluded.data, '$.keyvalue()[0]')->>'value')::NUMERIC
    )
);

或者,只需使用 MERGE

WITH updates AS (
    SELECT UNNEST($1::TEXT[]) AS _key,
           UNNEST($2::TEXT[]) AS field,
           UNNEST($3::NUMERIC[]) AS value
)
MERGE INTO legacy_hash AS lh
USING updates AS u
ON u._key = lh._key
WHEN NOT MATCHED THEN
  INSERT (_key, data)
  VALUES (u._key, jsonb_build_object(u.field, u.value))
WHEN MATCHED THEN
  UPDATE SET
    data = jsonb_set(
      lh.data,
      ARRAY[u.field],
      to_jsonb(COALESCE((lh.data->>u.field)::NUMERIC, 0) + u.value)
    )
;

db<>fiddle

BarÄ±Å UÅaklÄ± 1 年前

另一个有效的方法是,我没有将字段/值作为数组传递,而是将它们转换为JSON字符串。

INSERT INTO "legacy_hash" ("_key", "data")
SELECT k, d
FROM UNNEST($1::TEXT[], $2::JSONB[]) vs(k, d)
ON CONFLICT ("_key")
DO UPDATE SET "data" = (
    SELECT jsonb_object_agg(
        key, 
        CASE 
            WHEN jsonb_typeof(legacy_hash.data -> key) = 'number' 
                 AND jsonb_typeof(EXCLUDED.data -> key) = 'number'
            THEN to_jsonb((legacy_hash.data ->> key)::NUMERIC + (EXCLUDED.data ->> key)::NUMERIC)
            ELSE COALESCE(EXCLUDED.data -> key, legacy_hash.data -> key)
        END
    )
    FROM jsonb_each(legacy_hash.data || EXCLUDED.data) AS merged(key, value)
);