Hologres:INSERT ON CONFLICT (UPSERT)

Configurar a tabela de teste

BEGIN;
CREATE TABLE test1 (
    a int NOT NULL PRIMARY KEY,
    b int,
    c int
);
COMMIT;

INSERT INTO test1 VALUES (1, 2, 3);

Os exemplos a seguir são independentes entre si. Cada um parte do estado inicial acima.

InsertOrIgnore — ignorar em caso de conflito

Quando existe uma chave primária duplicada, a linha recebida é descartada.

INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO NOTHING;

Resultado:

a | b | c
1 | 2 | 3    -- unchanged

InsertOrUpdate — atualizar colunas específicas

Somente as colunas listadas em SET são atualizadas. As demais mantêm seus valores atuais.

-- Partial-column update: only b is updated, c keeps its value
INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO UPDATE SET b = excluded.b;

Resultado:

a | b | c
1 | 1 | 3    -- c unchanged

InsertOrUpdate — atualização de linha completa

Duas formas equivalentes:

-- Method 1: list all columns explicitly
INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO UPDATE SET b = excluded.b, c = excluded.c;

-- Method 2: use ROW(excluded.*) shorthand
INSERT INTO test1 (a, b, c) VALUES (1, 1, 1)
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);

Resultado:

a | b | c
1 | 1 | 1

InsertOrReplace — sobrescrever com null para colunas ausentes

Para substituir uma linha inteira e preencher colunas ausentes com null, passe null explicitamente na lista VALUES.

INSERT INTO test1 (a, b, c) VALUES (1, 1, null)
ON CONFLICT (a)
DO UPDATE SET b = excluded.b, c = excluded.c;

Resultado:

a | b | c
1 | 1 | \N

Upsert a partir de outra tabela

-- Prepare source table
CREATE TABLE test2 (
    d int NOT NULL PRIMARY KEY,
    e int,
    f int
);
INSERT INTO test2 VALUES (1, 5, 6);

-- Replace rows in test1 with matching rows from test2
INSERT INTO test1 (a, b, c)
SELECT d, e, f FROM test2
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);

Resultado:

a | b | c
1 | 5 | 6

Para remapear colunas (por exemplo, test2.e atualiza test1.c e test2.f atualiza test1.b):

INSERT INTO test1 (a, b, c)
SELECT d, e, f FROM test2
ON CONFLICT (a)
DO UPDATE SET (a, c, b) = ROW(excluded.*);

Resultado:

a | b | c
1 | 6 | 5

Erro: duplicate key value violates unique constraint / Update row with Key multiple times

Sintomas:

duplicate key value violates unique constraint
-- or --
Update row with Key (xxx)=(yyy) multiple times

Causa: Os dados de origem contêm linhas duplicadas. O exemplo a seguir reproduz o erro:

-- This fails: the source contains (1, 2, 3) twice
INSERT INTO test1 VALUES (1, 2, 3), (1, 2, 3)
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);
-- ERROR: internal error or constraint violation

Estado da tabela após o erro: test1 permanece inalterada.

Correção: Defina o flag keep-last para resolver linhas duplicadas na origem automaticamente:

set hg_experimental_affect_row_multiple_times_keep_last = on;

Como alternativa, elimine as duplicatas na origem antes do upsert (consulte Limitações).

Erro: chave duplicada causada por TTL expirado

Causa: Uma tabela de origem possui time to live (TTL) configurado. Após a expiração do TTL, linhas obsoletas podem não ser removidas imediatamente, deixando chaves primárias duplicadas na origem.

Correção: A partir do Hologres V1.3.23, use o comando a seguir para remover linhas com chave primária duplicada causadas por TTL expirado. A política padrão é keep-last. Este comando está disponível apenas no Hologres V1.3.23 e versões posteriores — se sua instância for de uma versão anterior, faça o upgrade primeiro.

Em princípio, chaves primárias não devem ser duplicadas. Este comando remove apenas PKs duplicadas causadas por TTL expirado, não duplicações gerais de PK.

call public.hg_remove_duplicated_pk('<schema>.<table_name>');

Exemplo:

BEGIN;
CREATE TABLE tbl_1 (a int NOT NULL PRIMARY KEY, b int, c int);
CREATE TABLE tbl_2 (d int NOT NULL PRIMARY KEY, e int, f int);
CALL set_table_property('tbl_2', 'time_to_live_in_seconds', '300');
COMMIT;

INSERT INTO tbl_1 VALUES (1, 1, 1), (2, 3, 4);
INSERT INTO tbl_2 VALUES (1, 5, 6);

-- After 300 seconds, insert a row with the same primary key into tbl_2.
INSERT INTO tbl_2 VALUES (1, 3, 6);

-- This fails: tbl_2 now has duplicate primary keys caused by TTL expiry.
INSERT INTO tbl_1 (a, b, c)
SELECT d, e, f FROM tbl_2
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);
-- ERROR: internal error: Duplicate keys detected when building hash table.

-- Clean up duplicate primary keys in tbl_2, then retry.
call public.hg_remove_duplicated_pk('tbl_2');

-- The import now succeeds.
INSERT INTO tbl_1 (a, b, c)
SELECT d, e, f FROM tbl_2
ON CONFLICT (a)
DO UPDATE SET (a, b, c) = ROW(excluded.*);

Erro: falta de memória (OOM)

Sintoma:

Total memory used by all existing queries exceeded memory limitation

Causa: A instância não possui memória suficiente para uma tarefa de escrita de grande volume.

Correção:

• Use Serverless Computing (Hologres V2.1.17+) para delegar a tarefa a recursos serverless, reduzindo o risco de OOM sem reservar capacidade adicional na instância.

• Siga as etapas descritas em Troubleshoot common OOM errors.