Use COPY ON CONFLICT para sobrescrever dados - AnalyticDB: Central de Documentação do Alibaba Cloud

Ao importar dados para o AnalyticDB for PostgreSQL com o comando COPY, a operação falha se alguma linha de entrada violar uma restrição existente na tabela. O recurso COPY ON CONFLICT estende a instrução padrão COPY FROM para tratar esses conflitos sem interromper a importação. Assim, é possível sobrescrever a linha conflitante ou ignorá-la silenciosamente.

O COPY ON CONFLICT tem suporte apenas em instâncias do AnalyticDB for PostgreSQL V6.0 com versão secundária do mecanismo 20210528 ou posterior. Atualize sua instância para a versão secundária mais recente do mecanismo antes de usar esse recurso. Para obter instruções, consulte Atualizar a versão secundária do mecanismo .

Pré-requisitos

Antes de começar, verifique se você tem:

Uma instância do AnalyticDB for PostgreSQL V6.0 com versão secundária do mecanismo 20210528 ou posterior
Uma tabela heap com chave primária ou restrição única definida como destino da importação

Limitações

A tabela de destino deve ser do tipo heap. Tabelas otimizadas para anexos (AO) não têm suporte porque não possuem índices únicos.
Tabelas particionadas têm suporte apenas na versão secundária do mecanismo 6.3.6.1 ou posterior. Para instruções de atualização, consulte Atualizar a versão secundária do mecanismo.
A tabela de destino não pode ser uma visão atualizável.
O comando COPY ON CONFLICT aceita apenas a direção COPY FROM. A direção COPY TO não tem suporte.
O parâmetro CONFLICT index não tem suporte. O COPY ON CONFLICT sempre verifica todas as restrições da tabela, em vez de um índice específico. Especificar um índice de conflito resulta no seguinte erro:
```
ERROR:  COPY ON CONFLICT does NOT support CONFLICT index params
```
O parâmetro UPDATE SET também não tem suporte. O comando COPY ON CONFLICT DO UPDATE sempre sobrescreve todas as colunas da linha conflitante, e não apenas um subconjunto. Especificar UPDATE SET gera o erro:
```
ERROR:  COPY ON CONFLICT does NOT support UPDATE SET targets
```

Sintaxe

COPY table [(column [, ...])] FROM {'file' | STDIN}
     [ [WITH]
       [BINARY]
       [OIDS]
       [HEADER]
       [DELIMITER [ AS ] 'delimiter']
       [NULL [ AS ] 'null string']
       [ESCAPE [ AS ] 'escape' | 'OFF']
       [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
       [CSV [QUOTE [ AS ] 'quote']
            [FORCE NOT NULL column [, ...]]
       [FILL MISSING FIELDS]
       [[LOG ERRORS]
       SEGMENT REJECT LIMIT count [ROWS | PERCENT] ]
    [DO ON CONFLICT DO UPDATE | NOTHING]

A cláusula de resolução de conflitos ao final determina o comportamento quando uma linha de entrada corresponde a uma restrição existente:

Cláusula	Comportamento
`DO ON CONFLICT DO UPDATE`	Substitui a linha existente por todos os valores das colunas dos dados de entrada.
`DO ON CONFLICT DO NOTHING`	Ignora a linha de entrada. A linha existente permanece inalterada.

Exemplo

O exemplo a seguir demonstra um cenário completo: importação de dados, ocorrência de um conflito de restrição e resolução com cada cláusula.

Etapa 1: Crie uma tabela

CREATE TABLE NATION (
    N_NATIONKEY  INTEGER,
    N_NAME       CHAR(25),
    N_REGIONKEY  INTEGER,
    N_COMMENT    VARCHAR(152),
    PRIMARY KEY (N_NATIONKEY)
);

A coluna N_NATIONKEY atua como chave primária e garante a unicidade em todas as linhas.

Etapa 2: Importar dados iniciais

COPY NATION FROM stdin;

Quando o prompt >> aparecer, insira as linhas abaixo. Use o caractere de tabulação para separar os valores das colunas.

0	'ALGERIA'	0	'haggle. carefully final deposits detect slyly agai'
1	'ARGENTINA'	1	'al foxes promise slyly according to the regular accounts. bold requests alon'
2	'BRAZIL'	1	'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
3	'CANADA'	1	'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
\.

Verifique a importação:

SELECT * FROM NATION;

Saída esperada:

 n_nationkey |          n_name           | n_regionkey |                                                 n_comment
-------------+---------------------------+-------------+------------------------------------------------------------------------------------------------------------
           2 | 'BRAZIL'                  |           1 | 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
           3 | 'CANADA'                  |           1 | 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
           0 | 'ALGERIA'                 |           0 | ' haggle. carefully final deposits detect slyly agai'
           1 | 'ARGENTINA'               |           1 | 'al foxes promise slyly according to the regular accounts. bold requests alon'
(4 rows)

Etapa 3: Observar um erro de conflito

A tentativa de inserir uma linha com N_NATIONKEY = 0 (valor que já existe) falha ao usar a instrução padrão COPY:

COPY NATION FROM stdin;

Insira a seguinte linha (separada por tabulação):

0	'GERMANY'	3	'l platelets. regular accounts x-ray: unusual, regular acco'
\.

A operação falha:

ERROR:  duplicate key value violates unique constraint "nation_pkey"
DETAIL:  Key (n_nationkey)=(0) already exists.
CONTEXT:  COPY nation, line 1

Etapa 4: Sobrescrever a linha conflitante

Use DO ON CONFLICT DO UPDATE para substituir a linha existente pelos novos dados de entrada. Quando ocorre um conflito, o comando COPY ON CONFLICT DO UPDATE sobrescreve a linha armazenada com todos os valores de coluna da linha recebida.

COPY NATION FROM stdin DO ON CONFLICT DO UPDATE;

Insira a mesma linha novamente (separada por tabulação):

0	'GERMANY'	3	'l platelets. regular accounts x-ray: unusual, regular acco'
\.

Nenhum erro é retornado. A linha com N_NATIONKEY = 0 é atualizada para refletir os novos valores:

SELECT * FROM NATION;

 n_nationkey |          n_name           | n_regionkey |                                                 n_comment
-------------+---------------------------+-------------+------------------------------------------------------------------------------------------------------------
           2 | 'BRAZIL'                  |           1 | 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
           3 | 'CANADA'                  |           1 | 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
           1 | 'ARGENTINA'               |           1 | 'al foxes promise slyly according to the regular accounts. bold requests alon'
           0 | 'GERMANY'                 |           3 | 'l platelets. regular accounts x-ray: unusual, regular acco'
(4 rows)

Etapa 5: Ignorar uma linha conflitante

Use DO ON CONFLICT DO NOTHING para descartar silenciosamente a linha de entrada quando houver um conflito:

COPY NATION FROM stdin DO ON CONFLICT DO NOTHING;

Insira uma linha com N_NATIONKEY = 1, que entra em conflito com a linha existente ARGENTINA (separada por tabulação):

1	'GERMANY'	3	'l platelets. regular accounts x-ray: unusual, regular acco'
\.

Nenhum erro é retornado. A linha com N_NATIONKEY = 1 permanece inalterada:

SELECT * FROM NATION;

 n_nationkey |          n_name           | n_regionkey |                                                 n_comment
-------------+---------------------------+-------------+------------------------------------------------------------------------------------------------------------
           2 | 'BRAZIL'                  |           1 | 'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly speci'
           3 | 'CANADA'                  |           1 | 'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'
           1 | 'ARGENTINA'               |           1 | 'al foxes promise slyly according to the regular accounts. bold requests alon'
           0 | 'GERMANY'                 |           3 | 'l platelets. regular accounts x-ray: unusual, regular acco'
(4 rows)