PostgREST can't upsert against partial unique indexes

The bug

I split a 3-column unique constraint into two partial unique indexes to support a new nullable column. The next day, every .upsert() against that table started failing with:

there is no unique or exclusion constraint matching the ON CONFLICT specification

The upsert call hadn't changed. The data shape was identical. The error message was technically correct and completely useless: the constraint did exist, it was just partial.

This post is the unblock for anyone hitting that error on a Supabase table that uses partial unique indexes.

The schema change

The original table had a flat 3-column unique constraint:

CREATE TABLE profile_subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  category_type TEXT NOT NULL,
  subcategory TEXT NOT NULL,
  enabled BOOLEAN DEFAULT TRUE,
  ...
  UNIQUE(profile_id, category_type, subcategory)
);

I added a nullable source_agency column. The intent: NULL means "this subscription applies to all agencies" (a default mode), and a non-NULL value means "this subscription is scoped to a specific agency."

Uniqueness now had two cases:

For NULL-agency rows, unique on (profile_id, category_type, subcategory).
For non-NULL-agency rows, unique on (profile_id, category_type, subcategory, source_agency).

A naive 4-column unique constraint doesn't work because PostgreSQL treats NULLs as "always distinct" by default. You'd get unlimited duplicate (profile, category, subcategory, NULL) rows.

The right tool is two partial unique indexes:

CREATE UNIQUE INDEX idx_profile_subs_null_agency
  ON profile_subscriptions (profile_id, category_type, subcategory)
  WHERE source_agency IS NULL;

CREATE UNIQUE INDEX idx_profile_subs_with_agency
  ON profile_subscriptions (profile_id, category_type, subcategory, source_agency)
  WHERE source_agency IS NOT NULL;

This works at the database level. Both indexes enforce the right uniqueness. Direct SQL INSERT and UPDATE against the table behave correctly. The migration ran clean. Tests passed. Everything looked fine.

Why PostgREST breaks

The Supabase JS client builds upsert requests like this:

supabase
  .from('profile_subscriptions')
  .upsert(rows, {
    onConflict: 'profile_id,category_type,subcategory,source_agency'
  })

PostgREST translates that into roughly:

INSERT INTO profile_subscriptions (...) VALUES (...)
ON CONFLICT (profile_id, category_type, subcategory, source_agency)
DO UPDATE SET ...

This SQL is syntactically valid. It still fails. PostgreSQL's ON CONFLICT requires the inference column list to match an existing unique constraint or unique index exactly, including any WHERE predicate.

Your unique index has WHERE source_agency IS NOT NULL. The upsert's ON CONFLICT (...) doesn't include the predicate. PostgreSQL can't find a matching constraint. You get:

there is no unique or exclusion constraint matching the ON CONFLICT specification

PostgREST won't generate ON CONFLICT (cols) WHERE source_agency IS NOT NULL from the client API. There's no way to express the predicate through upsert's onConflict option.

The obvious workaround, "just put a COALESCE(source_agency, '') in the index and make it non-partial," also doesn't work. PostgREST can't upsert against expression-based indexes either, for the same root reason.

The fix: delete-matching + insert

Rather than fight ON CONFLICT, I dropped down to two operations: delete the matching row(s) by exact predicate, then insert the new state. From apps/api/src/routes/subscriptions.ts:248:

// PostgREST cannot upsert against partial unique indexes (no WHERE clause
// support in ON CONFLICT). Use delete-matching + insert instead.
const nullAgencySubs = subscriptions.filter((s) => !s.source_agency);
const agencySubs = subscriptions.filter((s) => s.source_agency);

const allResults: ProfileSubscription[] = [];

if (nullAgencySubs.length > 0) {
  // Delete existing matching rows first
  await Promise.all(
    nullAgencySubs.map((sub) =>
      supabaseAdmin
        .from('profile_subscriptions')
        .delete()
        .eq('profile_id', profileId)
        .eq('category_type', sub.category_type)
        .eq('subcategory', sub.subcategory)
        .is('source_agency', null),
    ),
  );

  const rows = nullAgencySubs.map((sub) => ({
    profile_id: profileId,
    category_type: sub.category_type,
    subcategory: sub.subcategory,
    enabled: sub.enabled,
    inferred: sub.inferred,
    source_agency: null,
    updated_at: now,
  }));

  const { data, error } = await supabaseAdmin
    .from('profile_subscriptions')
    .insert(rows)
    .select();

  if (error) { /* handle error */ }
  allResults.push(...((data || []) as ProfileSubscription[]));
}

// agency-set bucket follows the same pattern with
// .eq('source_agency', sub.source_agency!) instead of .is('source_agency', null)

The pattern in three steps:

Split the input rows by whether the partial-index predicate applies (null-agency vs agency-set).
For each bucket, delete the matching existing rows. Use .is('source_agency', null) for the null bucket, because .eq() will never match NULL in PostgREST.
Insert the new rows in one batch per bucket.

This avoids ON CONFLICT entirely. The trade-offs:

You're doing roughly 2N round trips instead of N. For a small batch (these payloads are usually under 30 rows) it's fine. For high-volume writes, move it into a PL/pgSQL function and call it through Supabase RPC.
The window between DELETE and INSERT is not transactionally protected at the client level. If your write pattern allows two clients to race on the same (profile_id, category_type, subcategory), wrap the whole thing in a Supabase RPC and let Postgres serialize.

For our use case the writes happen during a quiz wizard flow where the user is the only writer on their own subscription rows. The race window is negligible.

The deeper trap

ORM abstractions over Postgres leak whenever your schema uses anything beyond column-level constraints. Partial unique indexes are common in production schemas. Anywhere you have NULL-with-meaning, the natural answer is a partial unique index:

Soft-deleted rows where the unique constraint should only apply to live rows
Multi-tenant uniqueness where tenant_id is nullable for global rows
Draft-vs-published states where the slug is unique only among published rows
Versioned rows where the index applies only to the latest version

The moment you reach for one, you lose:

.upsert() against the index via PostgREST or any client that wraps it
Any framework that emits INSERT ... ON CONFLICT (cols) without a predicate (most of them)
Codegen tools that scan the schema for unique constraints. Partial indexes often get treated as not-a-constraint from the codegen's perspective.

The fix is rarely interesting. The trap is that the failure mode is silent in the most damaging way: the test suite passes, the local SQL queries work, the migration runs clean. The bug only fires on the first real client call, and the error message points at the constraint as if it were missing, not partial.

If you've added a nullable column with meaningful semantics and the upserts go quiet, check whether you migrated the unique constraint into a partial index. Then pick one:

Drop down to delete + insert, like above
Move the operation into a PL/pgSQL function and call it via Supabase RPC
Petition PostgREST to support ON CONFLICT ... WHERE. It's a known gap: PostgREST/postgrest#2123 tracks index-predicate and exclusion-constraint upserts in core, and postgrest-js#403 mirrors it on the JS client. Both are still open.

Notes

This post grew out of fixing a real production bug at Nibble, where the schema in question backs per-agency subscription preferences across 13 countries of recall data. The shipped fix has been in production since Feb 2026 with no observed race issues.

If you've hit the same trap from a different direction (PostgREST + composite uniqueness + nullable columns), I'd be curious to hear it: github.com/Maliik-B or on X @Don_adijah.

推荐订阅源

DEV Community