Gerador de upsert

Upsert no DuckDB: INSERT OR REPLACE / ON CONFLICT DO UPDATE

Nomeie tabela, chave e colunas e receba a instrução que o DuckDB realmente aceita, com uma guarda para que uma linha antiga nunca sobrescreva uma nova. Abaixo: como a mecânica funciona, o que quebra e como a Datrise carrega o DuckDB de forma incremental.

As notas técnicas desta página estão em inglês.

Gere a instrução

Instrução · INSERT OR REPLACE / ON CONFLICT DO UPDATE

-- Target needs a PRIMARY KEY (or UNIQUE) on the conflict columns.
INSERT INTO "deals" ("id", "name", "stage", "amount", "owner_id", "updated_at")
SELECT "id", "name", "stage", "amount", "owner_id", "updated_at"
FROM "deals_staging"
ON CONFLICT ("id") DO UPDATE SET
  "name" = EXCLUDED."name",
  "stage" = EXCLUDED."stage",
  "amount" = EXCLUDED."amount",
  "owner_id" = EXCLUDED."owner_id",
  "updated_at" = EXCLUDED."updated_at"
WHERE "deals"."updated_at" IS NULL
   OR "deals"."updated_at" < EXCLUDED."updated_at";

-- Shorthand when every column should be replaced:
-- INSERT OR REPLACE INTO "deals" SELECT "id", "name", "stage", "amount", "owner_id", "updated_at" FROM "deals_staging";

Como o upsert funciona no DuckDB

DuckDB implements the Postgres upsert syntax, INSERT … ON CONFLICT DO UPDATE with EXCLUDED, plus the shorter INSERT OR REPLACE INTO. Both need a PRIMARY KEY or UNIQUE constraint on the conflict columns, and DuckDB does not add one when you create a table with CREATE TABLE AS, so declare the table explicitly first. The staging source can be a file: read_parquet or read_csv_auto works directly in the SELECT.

A DuckDB database file has a single writer. That suits a local or notebook load where one process owns the file; it does not suit several workers upserting at once. For a file others read, generate a complete snapshot with CREATE OR REPLACE TABLE … AS SELECT, or export Hive-partitioned Parquet and let readers dedupe, which is the snapshot pattern Datrise uses for this destination.

Antes de executar

DuckDB supports the Postgres ON CONFLICT syntax and the shorter INSERT OR REPLACE; both need a PRIMARY KEY or UNIQUE constraint, which DuckDB does not create by default on CREATE TABLE AS.
The staging "table" can be a file: FROM read_parquet('deals/*.parquet') or read_csv_auto(...) works directly in the SELECT.
A DuckDB file has one writer at a time. For a shared file, generate the full snapshot with CREATE OR REPLACE TABLE … AS and swap it, instead of concurrent upserts.

Perguntas frequentes

INSERT OR REPLACE or ON CONFLICT DO UPDATE?

OR REPLACE overwrites every column of the matched row. ON CONFLICT DO UPDATE lets you list columns and add the watermark WHERE, so it is the safer default.

Why does DuckDB say the table has no primary key for the conflict target?

CREATE TABLE AS does not create constraints. CREATE TABLE deals (id VARCHAR PRIMARY KEY, …) first, then insert.

Can two processes upsert into the same .duckdb file?

No, one writer at a time. Run the loads sequentially, or write per-process Parquet and merge with a single DuckDB session.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no DuckDB com exatamente esta mecânica, uma marca d'água sobre updated-at e colunas tipadas, então a instrução acima é a que roda por você. Entre na lista de espera para acesso antecipado.

Explorar o catálogo de integrações