The upsert said it worked. It wrote zero rows. Every single run.

#programming #coding #webdev #learning

Your scanner runs. It fetches data. It loops through rows, calls upsert, increments a success counter. It logs ✓ 42 rows processed. You check the table. Empty.

That ran every day for a while before we caught it.

Key Takeaways

ON CONFLICT (col) will not use a partial unique index (WHERE col IS NOT NULL) as the conflict arbiter. Postgres requires the predicate to be restated in the statement itself.
PostgREST's upsert path can't restate that predicate. Every row throws a quiet error.
A loop that only increments on success will report a perfectly healthy run that wrote absolutely nothing.
Fix: replace the partial index with a plain, unconditional unique index.

The setup

At Elevare Digital, ARIA runs data ingestion pipelines that pull from external sources and upsert into Postgres via Supabase. Standard stuff. One scanner's job was to fetch records and land them in a table, using a unique column as the conflict target so re-runs were idempotent.

The index on that column had been created like this, probably by someone being careful about NULLs:

CREATE UNIQUE INDEX records_external_id_idx
  ON records (external_id)
  WHERE external_id IS NOT NULL;

Reasonable-looking. Partial indexes are useful. This one quietly broke everything.

What Postgres actually requires

When you write:

INSERT INTO records (external_id, payload)
VALUES ('abc-123', '{...}')
ON CONFLICT (external_id) DO UPDATE
  SET payload = EXCLUDED.payload;

Postgres needs to identify which unique constraint or index to use as the arbiter. For a full unique index, ON CONFLICT (external_id) is enough — Postgres finds it.

For a partial unique index, Postgres requires the conflict target to include the index predicate:

INSERT INTO records (external_id, payload)
VALUES ('abc-123', '{...}')
ON CONFLICT (external_id) WHERE external_id IS NOT NULL  -- restate the predicate
DO UPDATE
  SET payload = EXCLUDED.payload;

Without that WHERE clause, Postgres cannot match the partial index. It doesn't fall back. It throws an error:

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

You can verify this yourself in a few lines:

-- Create a table with a partial unique index
CREATE TABLE demo (id serial, external_id text, payload text);
CREATE UNIQUE INDEX demo_ext_id_partial ON demo (external_id) WHERE external_id IS NOT NULL;

-- This fails — Postgres can't match the partial index
INSERT INTO demo (external_id, payload) VALUES ('x1', 'a')
ON CONFLICT (external_id) DO UPDATE SET payload = EXCLUDED.payload;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

-- This works — predicate restated
INSERT INTO demo (external_id, payload) VALUES ('x1', 'a')
ON CONFLICT (external_id) WHERE external_id IS NOT NULL
DO UPDATE SET payload = EXCLUDED.payload;
-- INSERT 0 1

Why PostgREST couldn't restate the predicate

ARIA calls Supabase's upsert via PostgREST. The request looks something like:

const { error } = await supabase
  .from('records')
  .upsert(rows, { onConflict: 'external_id' });

PostgREST translates onConflict: 'external_id' into:

ON CONFLICT (external_id) DO UPDATE ...

There's no interface in PostgREST's upsert path to append an arbitrary WHERE predicate to the conflict target. So every row-level upsert hit the same error. Every row.

Why the loop reported success

The ingestion loop looked roughly like this:

let successCount = 0;

for (const row of fetchedRows) {
  const { error } = await supabase
    .from('records')
    .upsert(row, { onConflict: 'external_id' });

  if (!error) {
    successCount++;
    // error case: nothing. no log, no throw, no increment.
  }
}

console.log(`✓ ${successCount} rows processed`);

When error was non-null, the loop just... continued. No log. No counter. The success counter never moved, but fetchedRows.length rows later, it logged a number that looked fine because the fetch itself succeeded.

Actually — it logged 0. But nobody was watching for zero. Zero looks like "nothing to sync today."

A counter that only moves on success will always look plausible. You need to also track failures:

let successCount = 0;
let errorCount = 0;

for (const row of fetchedRows) {
  const { error } = await supabase
    .from('records')
    .upsert(row, { onConflict: 'external_id' });

  if (error) {
    errorCount++;
    console.error('upsert failed:', error.message, row.external_id);
  } else {
    successCount++;
  }
}

console.log(`processed: ${successCount} ok, ${errorCount} failed of ${fetchedRows.length} fetched`);

if (errorCount > 0) {
  throw new Error(`ingestion completed with ${errorCount} errors`);
}

Now a run that writes nothing will at least be loud about it.

The fix

Drop the partial index. Create a plain one.

DROP INDEX records_external_id_idx;

CREATE UNIQUE INDEX records_external_id_idx
  ON records (external_id);

If NULLs are genuinely a concern, handle them at the application layer or with a NOT NULL constraint on the column. A partial index to "exclude NULLs" is not worth the silent failure mode when you need conflict arbitration.

After the index swap, the PostgREST upsert matched correctly and rows started landing.

The honest lesson

Postgres did nothing wrong here. The docs describe this requirement. The error message is clear. The problem was that the error was being swallowed and nothing downstream cared that zero rows were written.

Two independent things had to both be true for this to stay hidden:

A partial index being used as an ON CONFLICT target without the predicate
A loop that treated errors as a no-op

Either one alone might have surfaced faster. Together, they produced a system that appeared healthy and did nothing.

When you write an ingestion loop, the number that matters isn't "how many did I try" or "how many succeeded." It's the ratio. And if errors are silent, you'll never compute it.

— Mike Clarke, founder of Elevare Digital.