Generador de upsert

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

Nombra tu tabla, clave y columnas y obtén la sentencia que DuckDB acepta de verdad, con una guarda para que una fila antigua nunca sobrescriba una nueva. Debajo: cómo funciona la mecánica, qué se rompe y cómo Datrise carga DuckDB de forma incremental.

Las notas técnicas de esta página están en inglés.

Genera la sentencia

Sentencia · 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";

Cómo funciona el upsert en 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 ejecutarla

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.

Preguntas frecuentes

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.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en DuckDB con esta misma mecánica, una marca de agua sobre updated-at y columnas tipadas, así que la sentencia de arriba es la que corre por ti. Únete a la lista de espera para acceso anticipado.

Explorar el catálogo de integraciones