Início / dba / Perguntas / 129522
Accepted
Oliver Salzburg
Asked: 2016-02-17 07:59:22 +0800 CST

Como obter o ID da linha conflitante no upsert?

  • 772

Tenho uma tabela tagcom 2 colunas: id(uuid) e name(texto). Agora quero inserir uma nova tag na tabela, mas se a tag já existir, quero simplesmente obter o idregistro existente.

Presumi que poderia usar apenas ON CONFLICT DO NOTHINGem combinação com RETURNING "id":

INSERT INTO
    "tag" ("name")
VALUES( 'foo' )
ON CONFLICT DO NOTHING
RETURNING "id";

Mas isso retorna um conjunto de resultados vazio, se a tag com o nome "foo" já existir.

Em seguida, alterei a consulta para usar uma DO UPDATEcláusula noop:

INSERT INTO
    "tag" ("name")
VALUES( 'foo' )
ON CONFLICT ("name") DO UPDATE SET "name" = 'foo'
RETURNING "id";

Isso funciona como pretendido, mas é um pouco confuso, porque estou apenas definindo o nome para o valor já existente.

Esta é a maneira de resolver esse problema ou há uma abordagem mais simples que estou perdendo?

postgresql upsert

2 respostas

  1. Best Answer

    Isso funcionará (pelo que testei) em todos os 3 casos, se os valores a serem inseridos forem todos novos ou já estiverem na tabela ou uma mistura:

    WITH
  val (name) AS
    ( VALUES                          -- rows to be inserted
        ('foo'),
        ('bar'),
        ('zzz')
    ),
  ins AS
    ( INSERT INTO
        tag (name)
      SELECT name FROM val
      ON CONFLICT (name) DO NOTHING
      RETURNING id, name              -- only the inserted ones
    )
SELECT COALESCE(ins.id, tag.id) AS id, 
       val.name
FROM val
  LEFT JOIN ins ON ins.name = val.name
  LEFT JOIN tag ON tag.name = val.name ;

    Provavelmente existem outras maneiras de fazer isso, talvez sem usar a nova ON CONFLICTsintaxe.

    • 14

  2. Não faço ideia de como isso funcionará, mas apenas como outra opção para tentar, aqui está fazendo o mesmo da maneira antiga (sem ON CONFLICT):

    WITH items (name) AS (VALUES ('foo'), ('bar'), ('zzz')),
     added        AS
      (
        INSERT INTO tag (name)

        SELECT name FROM items
        EXCEPT
        SELECT name FROM tag

        RETURNING id
      )
SELECT id FROM added

UNION ALL

SELECT id FROM tag
WHERE name IN (SELECT name FROM items)
;

    Ou seja, insira apenas os nomes [únicos] não encontrados na tagtabela e retorne os IDs; combine isso com os IDs dos nomes que existem em tag, para a saída final. Você também pode jogar namena saída, conforme sugerido por ypercubeᵀᴹ , para saber qual ID corresponde a qual nome.

    • 4

relate perguntas