Generador de upsert

Upsert en Supabase: INSERT … ON CONFLICT DO UPDATE

Nombra tu tabla, clave y columnas y obtén la sentencia que Supabase 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 Supabase de forma incremental.

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

Genera la sentencia

Sentencia · INSERT … ON CONFLICT DO UPDATE

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

Cómo funciona el upsert en Supabase

Supabase is Postgres, so the upsert is INSERT … ON CONFLICT DO UPDATE, and the guarantees are the same: a unique index on the key and an atomic per-row write. What differs is the path the data takes. supabase-js exposes .upsert(rows, { onConflict: 'id' }) over PostgREST, which is convenient for a few hundred rows but subject to request size limits and to row-level security on the calling key.

For a sync job, connect directly to the database (the pooled connection string) with the service role, COPY into a staging table in a dedicated schema, and run the generated statement. Keep synced tables out of the public schema exposed by the API unless you have written RLS policies for them; a synced CRM table with no policy is readable by anyone holding the anon key.

Antes de ejecutarla

The conflict target needs a UNIQUE index or constraint on exactly those columns; without one Postgres raises "there is no unique or exclusion constraint matching the ON CONFLICT specification".
EXCLUDED is the row that would have been inserted. The WHERE after DO UPDATE runs per conflicting row, so a stale row (older updated-at) is skipped rather than overwriting fresher data.
PostgreSQL 15 added MERGE for multi-branch logic (WHEN NOT MATCHED BY SOURCE … DELETE), but MERGE is not concurrency-safe under two writers; ON CONFLICT is the atomic upsert.

Preguntas frecuentes

supabase-js .upsert() or SQL?

.upsert() is fine for small interactive writes. For batches of thousands of rows use SQL over a direct connection: fewer round trips, no PostgREST payload limits, and the watermark guard.

Does row-level security apply to upserts?

Yes, when the write comes through the API with the anon or authenticated key. The service role and direct database connections bypass RLS, which is what a server-side sync should use.

What does ignoreDuplicates do?

It maps to ON CONFLICT DO NOTHING: existing rows are left untouched instead of updated. Use it for append-only facts, not for CRM records that change.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en Supabase 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