Início / dba / Perguntas / 307604
Accepted
IBot
Asked: 2022-02-17 15:31:41 +0800 CST

Upsert em uma tabela que possui vários campos e coluna jsonb usando a instrução WITH

  • 772

Então, eu estava tentando fazer um upsert com base em um resultado da consulta:

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

Há um problema:

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

Upsert com dados simulados está funcionando, mas não funciona enviando um parâmetro para jsonb_build_object

postgresql insert

1 respostas

  1. Best Answer

    Você provavelmente quer algo assim:

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

    ① Sempre forneça uma lista de colunas de destino para código persistente.

    ② A questão principal: você tem que SELECTsair do CTE para trazer os valores computados. Sua sintaxe original também mudou de destino e origem. Dê outra olhada na INSERTsintaxe .

    ③ Supondo que você quis dizer jsonb_build_object(r.thing_type, r.thing_count). O que você tinha lá não funcionou. Além disso, sem cast: jsonb_build_object()já retorna jsonb.

    ④ Meu palpite. O que você tinha, não funcionou. Observe o uso da EXCLUDEDpalavra-chave para se referir à linha inicialmente proposta para inserção.

    • 0

relate perguntas