主页 / dba / 问题 / 307604
Accepted
IBot
Asked: 2022-02-17 15:31:41 +0800 CST

使用 WITH 语句对具有多个字段和 jsonb 列的表进行 Upsert

  • 772

所以我试图根据查询结果进行更新：

/*
    Querying from this table:
     id | arrive_date | arrive_location | thing_type | thing_count
*/
CREATE TABLE IF NOT EXISTS public.inventory
(
  inventory_id serial NOT NULL,
  inventory_date date NOT NULL,
  arrive_location character varying NOT NULL,
  thing_type integer NOT NULL,
  quantity integer NOT NULL,
  PRIMARY KEY (inventory_id)
)    
/*
    Trying to insert on this table, where summary is a jsonb type:
    id | arrive_date | arrive_location | data
*/
CREATE TABLE IF NOT EXISTS public.preprocess_things
   (
      preprocess_id serial NOT NULL,
      arrive_date date NOT NULL,
      arrive_location character varying NOT NULL,
      data jsonb NOT NULL,
      CONSTRAINT preprocess_things_pkey PRIMARY KEY (preprocess_id),
      CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key   UNIQUE (arrive_date, arrive_location)
    )
/*Begin upsert*/
WITH result_query AS (
    SELECT DATE_TRUNC('day', inventory_date) AS arrive_date,
      arrive_location,
      thing_type,
      SUM(quantity) AS total_things
    FROM inventory
    GROUP BY arrive_date, arrive_location, thing_type
)
INSERT INTO preprocess_things (
    result_query.arrive_date,
    result_query.arrive_location,
    jsonb_build_object(result_query.thing_type || '', result_query.total_things)::jsonb
  ) ON CONFLICT (arrive_date, arrive_location) DO
UPDATE
  SET data= jsonb_insert(data, '{' || result_query.thing_type || '}', result_query.thing_sum)

有一个问题：

ERROR:  syntax error at or near "("
LINE 7:     jsonb_build_object(result_query.thing_type || '', total_things)::...

使用模拟数据的 Upsert 可以正常工作，但无法向 jsonb_build_object 发送参数

postgresql insert

1 个回答

  1. Best Answer

    你可能想要这样的东西：

    WITH result_query AS (
   SELECT id, arrive_date, arrive_location, thing_type, thing_count
   FROM   ... -- ?
   )
INSERT INTO preprocess_things (arrive_date, arrive_location, data)  -- ①
SELECT r.arrive_date  -- ②
     , r.arrive_location
     , jsonb_build_object(r.thing_type, r.thing_count)  -- ③
FROM   result_query r
ON     CONFLICT (arrive_date, arrive_location) DO
UPDATE
SET    data = data || EXCLUDED.data;  -- ④

    ① 始终为持久化代码提供目标列列表。

    ② 主要问题：您必须SELECT从 CTE 中引入计算值。您的原始语法也切换了目标和源。再看一下INSERT语法。

    ③假设你的意思jsonb_build_object(r.thing_type, r.thing_count)。你在那里的东西没有用。此外，没有演员：jsonb_build_object()已经返回jsonb。

    ④ 我的猜测。你所拥有的，没有用。请注意使用EXCLUDED关键字来指代最初建议插入的行。

    • 0

相关问题