Início / dba / Perguntas / 307645
Accepted
IBot
Asked: 2022-02-18 06:21:49 +0800 CST

UPSERT com UPDATE na coluna variável dá erro "o comando não pode afetar a linha uma segunda vez"

  • 772

Ei, estou tentando inserir ou atualizar (se as restrições estiverem duplicadas) com base em um resultado da consulta, estas são a instrução create table:

CREATE TABLE IF NOT EXISTS public.inventory (
  inventory_id serial PRIMARY KEY,
  arrive_date date NOT NULL,
  arrive_location character varying NOT NULL,
  thing_type integer NOT NULL,
  quantity integer NOT NULL
);

CREATE TABLE IF NOT EXISTS public.preprocess_things (
  preprocess_id serial PRIMARY KEY,
  arrive_date date NOT NULL,
  arrive_location character varying NOT NULL,
  data jsonb NOT NULL,
  CONSTRAINT preprocess_things_arrive_date_arrive_location_bo_key UNIQUE (arrive_date, arrive_location)
);

Esta é a consulta upsert:

WITH result_query AS (
    SELECT DATE_TRUNC('day', arrive_date) AS date,
      arrive_date,
      arrive_location,
      thing_type,
      SUM(quantity) AS total_things
    FROM inventory
    GROUP BY date, arrive_location, thing_type
)
INSERT INTO preprocess_things (
    arrive_date,
    arrive_location,
    data
  )
SELECT r.date AS arrive_date,
  r.arrive_location,
  jsonb_build_object(r.thing_type, r.total_things)
FROM result_query r
ON CONFLICT (arrive_date, arrive_location) DO
UPDATE SET data = preprocess_things.data || EXCLUDED.data

As linhas result_query são:

date                   | arrive_location | thing_type | thing_count
2018-05-30 00:00:00-00 | location_00     |   3        | 2
2018-05-31 00:00:00-00 | location_00     |   3        | 8
2018-05-31 00:00:00-00 | location_00     |   4        | 7

Tentando inserir em preprocess_things, onde data é um jsonbtipo e o resultado esperado é:

id | arrive_date            | arrive_location | data
1  | 2018-05-30 00:00:00-00 | location_00     | { "3": 2 }
2  | 2018-05-31 00:00:00-00 | location_00     | { "3": 8, "4": 7 }
postgresql duplication

1 respostas

  1. Best Answer

    Problema

    Seu CTE result_queryproduz duas linhas com os mesmos valores para (arrive_date, arrive_location). O primeiro é inserido, o segundo gera um conflito no INSERTe tenta UPDATEa mesma linha que acabou de ser inserida - o que não é possível, como a mensagem de erro informa. Se a linha já existir, você receberá o mesmo erro ao tentar atualizar a mesma linha duas vezes.
    Para mais explicações, consulte:

    Solução

    Dobre as duplicatas no SELECT, seja no CTE diretamente, ou no SELECTanexo ao INSERT. Você não divulgou como lidar com duplicatas exatamente, mas desde que você adotou minha sugestão com UPDATE, preprocess_things.data || EXCLUDED.datasuponho que você queira substituir as mesmas chaves (mesmo thing_type) pelo mesmo (arrive_date, arrive_location), mas mesclar todas as chaves distintas (distintas thing_type). Eu faço isso jsonb_object_agg()no CTE. A concatenação nas UPDATEsubstitui as mesmas chaves.

    WITH result_query AS (
   SELECT arrive_date, arrive_location
        , jsonb_object_agg(thing_type, total_things)  -- !
   FROM  (
      SELECT date_trunc('day', arrive_date) AS arrive_date
             -- additional column "arrive_date" had to go
           , arrive_location
           , thing_type
           , sum(quantity) AS total_things
      FROM   inventory
      GROUP  BY date_trunc('day', arrive_date), arrive_location, thing_type
      ) sub
   GROUP  BY arrive_date, arrive_location
   )
INSERT INTO preprocess_things AS p (arrive_date, arrive_location, data)
TABLE  result_query
ON CONFLICT (arrive_date, arrive_location) DO
UPDATE
SET    data = p.data || EXCLUDED.data
WHERE  p.data IS DISTINCT FROM p.data || EXCLUDED.data;  -- exclude empty updates

    db<>fique aqui

    Tudo é preparado no CTE, então usei a sintaxe curta TABLE result_queryno arquivo INSERT. Sobre isso:

    Sobre a WHEREcláusula adicionada para suprimir atualizações vazias:

    • 1

relate perguntas