内容的栏目 * @param int $category 0列表 1频道 2单页 3外链 * @return array */ function category_list($forumlist, $model = 0, $display = 0, $category = 0) { if (empty($forumlist)) return NULL; static $cache = array(); $key = $model . '-' . $display . '-' . $category; if (isset($cache[$key])) return $cache[$key]; if ($display) { foreach ($forumlist as $k => $val) { if (1 == $val['display'] && 1 == $val['type'] && $val['category'] == $category) { $cache[$key][$k] = $val; } } } else { foreach ($forumlist as $k => $val) { if (1 == $val['type'] && $val['category'] == $category) { $cache[$key][$k] = $val; } } } return empty($cache[$key]) ? NULL : $cache[$key]; } /** * @param $forumlist 所有版块列表 不分模型 * @param int $display 0全部CMS栏目 1在首页和频道显示内容的栏目 * @param int $category 0列表 1频道 2单页 3外链 * @return array */ function category_list_show($forumlist, $display = 0, $category = 0) { if (empty($forumlist)) return NULL; static $cache = array(); $key = $display . '-' . $category; if (isset($cache[$key])) return $cache[$key]; if ($display) { foreach ($forumlist as $k => $val) { if (1 == $val['display'] && 1 == $val['type'] && $val['category'] == $category) { $cache[$key][$k] = $val; } } } else { foreach ($forumlist as $k => $val) { if (1 == $val['type'] && $val['category'] == $category) { $cache[$key][$k] = $val; } } } return empty($cache[$key]) ? NULL : $cache[$key]; } /** * @param $forumlist 所有版块列表 * @return mixed BBS栏目数据(仅列表) 尚未开放bbs频道功能 */ function forum_list($forumlist) { if (empty($forumlist)) return array(); static $cache = array(); if (isset($cache['bbs_forum_list'])) return $cache['bbs_forum_list']; $cache['bbs_forum_list'] = array(); foreach ($forumlist as $_fid => $_forum) { if ($_forum['type']) continue; $cache['bbs_forum_list'][$_fid] = $_forum; } return $cache['bbs_forum_list']; } // 导航显示的版块 function nav_list($forumlist) { if (empty($forumlist)) return NULL; static $cache = array(); if (isset($cache['nav_list'])) return $cache['nav_list']; foreach ($forumlist as $fid => $forum) { if (0 == $forum['nav_display']) { unset($forumlist[$fid]); } } return $cache['nav_list'] = $forumlist; } ?>sql - How to use values from CTE in ON CONFLICT UPDATE - Stack Overflow

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>sql - How to use values from CTE in ON CONFLICT UPDATE - Stack Overflow

    sql - How to use values from CTE in ON CONFLICT UPDATE - Stack Overflow

    programmeradmin0浏览0评论

    I have a table that has 2 columns _key (string) and data (json), I am trying to upsert data into the table and getting error

    missing FROM-clause entry for table "updates"

    The error goes away if I remove the DO UPDATE SET and replace it with DO NOTHING.

    I have also tried using EXCLUDED.field & EXCLUDED.value in the UPDATE section, but that results in another error

    column excluded.field does not exist

    Here is the query I am trying to fix, if there is a conflict the query is supposed to update the json object so that the column is set to the 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)
)

    I have a version that works on a single (key, field, value) below

    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)
)

    I have a table that has 2 columns _key (string) and data (json), I am trying to upsert data into the table and getting error

    missing FROM-clause entry for table "updates"

    The error goes away if I remove the DO UPDATE SET and replace it with DO NOTHING.

    I have also tried using EXCLUDED.field & EXCLUDED.value in the UPDATE section, but that results in another error

    column excluded.field does not exist

    Here is the query I am trying to fix, if there is a conflict the query is supposed to update the json object so that the column is set to the 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)
)

    I have a version that works on a single (key, field, value) below

    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)
)
    • sql
    • postgresql
    Share Improve this question edited Feb 2 at 8:57 marc_s 755k184 gold badges1.4k silver badges1.5k bronze badges asked Feb 2 at 2:41 Barış UşaklıBarış Uşaklı 13.5k7 gold badges44 silver badges69 bronze badges 2
    • Please update the post to include a full minimal reproducible example, including relevant table DDL, example data, and expected results. Minimizing the effort required to reproduce an iissue will maximize the willingness of others to help. Don't unnecessarily wrap identifiers in "s: doing so introduces visual noise that makes queries more diffiicult to read. – JohnH Commented Feb 2 at 3:36
    • Please edit the tags to include the specific version of PortgreSQL. Versions from 15 on support merge which is much more flexible than INSERT ... ON CONFLICT DO UPDATE. – JohnH Commented Feb 2 at 4:14
    Add a comment  | 

    2 Answers 2

    Reset to default 1

    Unfortunately, the only tables you can access in the ON CONFLICT DO clause are the current row values (as the table name, or alias if any), and the proposed but failing row (as the excluded table).

    The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to the row proposed for insertion using the special excluded table.

    But in your case, you can rebuild what you need in a more convoluted way, by breaking down the jsonb to get your values out. You can use the JSONPath function keyvalue() to get the keys and values of an object as a JSONB array.

    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
    )
);

    Alternatively, just use a 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

    Another one that works, instead of passing the fields/values as an array I've turned them into JSON strings.

    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)
);

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论