Gerador de upsert

Upsert no Supabase: INSERT … ON CONFLICT DO UPDATE

Nomeie tabela, chave e colunas e receba a instrução que o Supabase 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 Supabase de forma incremental.

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

Gere a instrução

Instrução · 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";

Como o upsert funciona no 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 executar

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.

Perguntas frequentes

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.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no Supabase 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