Como usar INSERT ON CONFLICT para sobrescrever dados - AnalyticDB: Central de Documentação do Alibaba Cloud

O comando INSERT ON CONFLICT executa uma operação de upsert: insere uma nova linha quando não há conflito ou atualiza a linha existente ao detectar um conflito de chave primária ou índice único. Esse mecanismo evita falhas de gravação causadas por conflitos de chave primária durante a sincronização de dados ou importações em massa. O recurso é semelhante à instrução REPLACE INTO do MySQL. Este tópico descreve a sintaxe do INSERT ON CONFLICT e apresenta exemplos de uso.

Limitações

Tabelas particionadas: Suporte disponível apenas em instâncias com versão secundária V6.3.6.1 ou posterior.

Visualize a versão secundária da sua instância na página Basic Information do console AnalyticDB for PostgreSQL. Para atualizar, consulte Update the minor version of an instance .
Tipos de tabela suportados: Apenas tabelas Heap e Beam. Tabelas colunares (AO/AOCS) não aceitam índices únicos e, portanto, não são suportadas.
Chaves duplicadas em uma única instrução: Uma única instrução INSERT não pode incluir múltiplas linhas com a mesma chave primária. Trata-se de uma restrição do padrão SQL.

Sintaxe

Sintaxe básica

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT [ conflict_target ] conflict_action

Sintaxe completa

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be:
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action can be:
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ]

Parâmetros

Parâmetro	Descrição
`conflict_target`	Defina o que constitui um conflito. Para `DO NOTHING`: opcional — se omitido, todas as restrições utilizáveis e índices únicos são verificados. Para `DO UPDATE`: obrigatório — deve identificar a chave primária ou o índice único que aciona o conflito.
`conflict_action`	Determina a ação a ser tomada quando um conflito é detectado. `DO NOTHING` descarta a linha proposta. `DO UPDATE` atualize a linha existente usando a cláusula `DO UPDATE SET`; referencie os valores da linha proposta por meio da pseudo-tabela `excluded`. `DO UPDATE ALL` realiza uma atualização de linha completa e é obrigatório para tabelas Beam.

Restrições para DO UPDATE:

Não é possível atualizar a chave de distribuição ou a chave primária na cláusula SET.
A cláusula WHERE não aceita subconsultas.
As tabelas Beam não suportam atualizações parciais de colunas — utilize DO UPDATE ALL para atualizações de linha completa.

Exemplos

Os exemplos utilizam uma tabela t1 com as colunas a int PRIMARY KEY, b int, c int, d int DEFAULT 0.

Configure a tabela de teste

Crie a tabela t1:

CREATE TABLE t1 (
  a int PRIMARY KEY,
  b int,
  c int,
  d int DEFAULT 0
);

(Apenas tabelas Beam) Se você utiliza o mecanismo de armazenamento Beam e precisa de atualizações parciais de colunas em caso de conflito, altere o método de acesso da tabela para heap:
```
ALTER TABLE t1 SET ACCESS METHOD heap;
```
Insira uma linha inicial:
```
INSERT INTO t1 VALUES (0,0,0,0);
```

Verifique os dados:

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 row)

O problema de conflito

Um INSERT padrão com uma chave primária duplicada resulta em falha:

INSERT INTO t1 VALUES (0,1,1,1);

ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(0) already exists.

A cláusula ON CONFLICT resolve essa situação ignorando a linha conflitante ou atualizando-a.

Ignorar a linha conflitante

Utilize DO NOTHING para pular a inserção quando ocorrer um conflito. A linha existente permanece inalterada.

INSERT INTO t1
VALUES (0,1,1,1)
ON CONFLICT DO NOTHING;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
(1 row)

Atualizar todas as colunas exceto a chave primária em caso de conflito

Use DO UPDATE SET para sobrescrever a linha existente. A pseudo-tabela excluded contém os valores da inserção proposta.

INSERT INTO t1
VALUES (0,1,1,1)
ON CONFLICT (a)
DO UPDATE SET
  (b, c, d) = (excluded.b, excluded.c, excluded.d);

Alternativamente, especifique cada coluna individualmente:

INSERT INTO t1
VALUES (0,1,1,1)
ON CONFLICT (a)
DO UPDATE SET
  b = excluded.b, c = excluded.c, d = excluded.d;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 1 | 1 | 1
(1 row)

Atualizar colunas específicas em caso de conflito

Para atualizar apenas um subconjunto de colunas, liste somente essas colunas na cláusula SET. As colunas não listadas mantêm seus valores atuais.

Primeiro, redefina os dados de teste:

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

Sobrescrever apenas a coluna c:

INSERT INTO t1
VALUES (0,3,3,3)
ON CONFLICT (a)
DO UPDATE SET
  c = excluded.c;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 1 | 3 | 1
(1 row)

Incrementar uma coluna com base no valor atual:

INSERT INTO t1
VALUES (0,0,1,0)
ON CONFLICT (a)
DO UPDATE SET
  c = t1.c + 1;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 1 | 4 | 1
(1 row)

Redefinir uma coluna para seu valor padrão em caso de conflito

Utilize DEFAULT na cláusula SET para restaurar o valor padrão definido de uma coluna.

Primeiro, redefina os dados de teste:

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

INSERT INTO t1
VALUES (0,0,2,2)
ON CONFLICT (a)
DO UPDATE SET
  d = DEFAULT;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 1 | 1 | 0
(1 row)

A coluna d foi redefinida para 0 (seu valor padrão), enquanto b e c permaneceram inalteradas.

Fazer upsert de múltiplas linhas

Uma única instrução INSERT pode realizar upsert de várias linhas. Cada linha é avaliada independentemente conforme a política de conflito.

Primeiro, redefina os dados de teste:

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

Ignorar linhas conflitantes e inserir novas linhas:

INSERT INTO t1
VALUES (0,2,2,2), (3,3,3,3)
ON CONFLICT DO NOTHING;

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 3 | 3 | 3 | 3
 0 | 1 | 1 | 1
(2 rows)

A linha (0,2,2,2) foi ignorada porque a = 0 já existe. A linha (3,3,3,3) foi inserida normalmente.

Atualizar linhas conflitantes e inserir novas linhas:

INSERT INTO t1
VALUES (0,0,0,0), (4,4,4,4)
ON CONFLICT (a)
DO UPDATE SET
  (b, c, d) = (excluded.b, excluded.c, excluded.d);

SELECT * FROM t1;

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 3 | 3 | 3 | 3
 4 | 4 | 4 | 4
(3 rows)

Fazer upsert a partir de uma subconsulta

O comando INSERT ON CONFLICT funciona com INSERT INTO ... SELECT para mesclar dados entre tabelas. Essa abordagem é útil para pipelines ETL e fluxos de trabalho de mesclagem de tabelas.

Redefina os dados de teste:

DELETE FROM t1 WHERE a != 0;

UPDATE t1 SET b = 1, c = 1, d = 1 WHERE a = 0;

Crie uma tabela de source e insira dados:

CREATE TABLE t2 (LIKE t1);

INSERT INTO t2 VALUES (0,11,11,11), (2,22,22,22);

Mescle t2 em t1. Em caso de conflito, sobrescreva as colunas que não são chave primária:

INSERT INTO t1
SELECT * FROM t2
ON CONFLICT (a)
DO UPDATE SET
  (b, c, d) = (excluded.b, excluded.c, excluded.d);

SELECT * FROM t1;

 a | b  | c  | d
---+----+----+----
 2 | 22 | 22 | 22
 0 | 11 | 11 | 11
(2 rows)

Atualização de linha completa para tabelas Beam

Tabelas Beam não suportam atualizações parciais de colunas. Utilize DO UPDATE ALL para atualizar toda a linha conflitante.

Crie uma tabela Beam:

CREATE TABLE beam_test (
  a int PRIMARY KEY,
  b int,
  c int,
  d int DEFAULT 0
) USING beam;

Insira dados iniciais:

INSERT INTO beam_test VALUES (0,0,0,0), (1,1,1,1), (2,2,2,2);

Verifique os dados:

SELECT * FROM beam_test;

 a | b | c | d
---+---+---+---
 0 | 0 | 0 | 0
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
(3 rows)

Faça upsert de uma linha conflitante usando DO UPDATE ALL:

INSERT INTO beam_test
VALUES (0,4,4,4)
ON CONFLICT (a)
DO UPDATE ALL;

Verifique o resultado:

SELECT * FROM beam_test;

 a | b | c | d
---+---+---+---
 0 | 4 | 4 | 4
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
(3 rows)

Perguntas frequentes

Como verifico o mecanismo de armazenamento de uma tabela?

Consulte os catálogos de sistema pg_class e pg_am. Substitua schemaname.tablename pelo nome totalmente qualificado da sua tabela:

SELECT
  c.oid::regclass AS rel,
  coalesce(a.amname, 'heap') AS table_am
FROM pg_class c
LEFT JOIN pg_am a ON a.oid = c.relam
WHERE c.oid = 'schemaname.tablename'::regclass
  AND c.relkind = 'r';

Por que minha tabela Beam falha com "DO UPDATE SET is not supported for beam relations"?

Problema: A tentativa de realizar uma atualização parcial via INSERT ON CONFLICT em uma tabela Beam falha com o seguinte erro:

ERROR:  INSERT ON CONFLICT DO UPDATE SET is not supported for beam relations
HINT:  Please use INSERT INTO table VALUES(?,?,...) ON CONFLICT DO UPDATE ALL.

O mecanismo de armazenamento Beam suporta apenas atualizações de linha completa. Substitua DO UPDATE SET ... por DO UPDATE ALL:

-- Before (fails on Beam tables)
INSERT INTO beam_test VALUES (0,4,4,4)
ON CONFLICT (a)
DO UPDATE SET b = excluded.b, c = excluded.c;

-- After (works on Beam tables)
INSERT INTO beam_test VALUES (0,4,4,4)
ON CONFLICT (a)
DO UPDATE ALL;