PostgreSQL Found a Conflict. Why Did RETURNING Produce No Row?

A seemingly successful INSERT ... ON CONFLICT DO UPDATE WHERE false RETURNING * can be a misleading signal: no error, but no row in RETURNING. In practice the row was locked by the update clause but skipped due to the false predicate, so it won’t be returned.

We define a precise acceptance contract and run versioned tests to distinguish a false-positive “success” from a real insert/update. We use a disposable Postgres 15+ fixture and SQL scripts with synthetic inputs (unique keys, values, etc.). An independent manifest spells out for each case what should happen. A per-case evidence ledger will record expected vs observed outcomes. We include a negative control (plain INSERT), failure-injection (rollbacks, conflict-error), and reconcile final table state. Along the way we cite documentation (INSERT/RETURNING, transactions, locks) and related Refonte playbooks. Our final decision matrix will guide Accept / Repair / Rerun / Hold. No unexecuted or skipped case is “assumed correct”: all are labeled EXPECTED / NOT EXECUTED.

Ultimately, this is about operational ownership: if no row is returned, the caller’s contract should have anticipated it. (For perspective, Refonte’s PostgreSQL DO NOTHING RETURNING concurrency scenario is similar but uses DO NOTHING. Here we focus on DO UPDATE with a false WHERE.)

1. Define the acceptance contract

The acceptance contract specifies exactly what the INSERT statement should guarantee, independent of its superficial success message. We separate database invariants from caller expectations. Key points:

• Unique-Key Invariant: At most one row in the table may have a given natural key (our keycol) at any time. No duplicate keys; thus keycol is UNIQUE.

• Caller’s Output Contract: On success, the caller expects to receive the authoritative row’s identifier. That means: if the INSERT created a new row, RETURNING must return its id. If the INSERT found a conflict and updated an existing row, RETURNING must return the existing row’s id. (This mirrors the “lookup” contract in DO NOTHING helper.) Silence (no returned row) is not acceptable under the caller’s contract unless explicitly documented.

• UPDATE or INSERT Guarantee: The SQL ON CONFLICT DO UPDATE clause guarantees that either a new row is inserted or an existing row is updated (never both, never none) in one atomic statement. Therefore, after the statement, the table must contain a row with that key.

• RETURNING Behavior: By design, RETURNING only produces rows that were actually inserted or updated. In particular, if the existing row was locked but not updated (because the WHERE predicate was false), then no row will be returned. The contract must account for this documented guarantee.

With these invariants, we enumerate test cases

• Case 1 (No Conflict): Inserting a new key. Expect a new row and RETURNING should return it.

• Case 2 (Conflict, UPDATE Executes): Inserting a duplicate key when the WHERE clause is true. Expect the existing row to be updated and RETURNING should return it.

• Case 3 (Conflict, UPDATE Skipped): Inserting a duplicate key when the WHERE clause is false. As docs warn, the row is locked but not updated, so RETURNING should yield no row. That is expected database behavior, but the contract should treat “no row returned” as a distinct outcome that must be handled.

• Case 4 (Conflict Without ON CONFLICT): Inserting a duplicate key with no ON CONFLICT clause. Expect a unique-constraint error. RETURNING is not run.

• Case 5 (Rollback Scenario): Attempt a conflict where false inside a transaction, then roll back. Expect no net change in the table (original row intact).

Acceptance assertions: (1) After any success case, the table has exactly one row with the given key; (2) the id of that row is correctly returned if and only if an insert or update occurred. In short, the SQL command should either insert or update a single row, and return its id. If it returns none, that fact should only occur under the (recognized) WHERE=false scenario, which the caller must not ignore.

2. Record the versioned baseline

Before tests, capture the exact environment. Use pinned versions (e.g. PostgreSQL 15.4) so behavior is reproducible. Record this in an “environment” manifest:

mkdir -p evidence
psql --version     # e.g. "psql (PostgreSQL) 15.4"

-- Connect as test user
SELECT version() AS server_build;   -- e.g. "PostgreSQL 15.4"
SHOW default_transaction_isolation; -- should be "read committed" (Postgres default)
SHOW lock_timeout;                 -- check any custom timeouts (expected none)
SHOW statement_timeout;

These commands establish the postgres version and default settings. Our baseline:

• Version: Confirm psql and server are the same major version (15.4).

• Isolation Level: By default, Postgres uses Read Committed. We note that, as PostgreSQL’s transaction isolation documentation explains, each statement sees a fresh snapshot of committed data. This means our conflict checks behave as per Read Committed rules.

• Lock Settings: We use default lock timeouts (none). This is fine for our short tests.

No deviation from these values is expected. Any discrepancy (e.g. a different isolation or an error listing version) indicates a misconfigured environment. In that case, we HOLD: do not proceed until the environment is fixed.

Isolate the negative control

Negative control (Case NC): Execute a simple INSERT with no conflict path:

-- CASE NC: Control insert (new key, no conflict)
INSERT INTO conflict_fixture (keycol, value)
VALUES (200, 'alpha')
RETURNING id;
-- EXPECTED: 1 row returned with a new id. (If not, environment/test setup is broken.)

Assertion: The control insert must return exactly one row. If it returns none or errors out, reject the test setup (HOLD). This verifies the table and RETURNING are working as intended before we test conflict cases.

3. Build the disposable test fixture

Create a synthetic table and seed data in a throwaway database or schema. The schema is minimal: one serial id, one unique natural key, and a value column. For example:

-- LAB SETUP: Create table for conflict tests
DROP TABLE IF EXISTS conflict_fixture;
CREATE TABLE conflict_fixture (
  id SERIAL PRIMARY KEY,
  keycol INT UNIQUE,
  value TEXT
);

We then insert a row to trigger a conflict in later tests:

-- Seed an initial row (keycol = 100)
INSERT INTO conflict_fixture (keycol, value)
VALUES (100, 'initial');

All operations will use this fresh table. After the lab, the fixture can be dropped to avoid persistent side effects. (This approach is similar to the synthetic tables built in SQLite partial index UPSERT; we choose data values arbitrarily to force conflict.)

Check: The table creation should succeed silently. The UNIQUE constraint on keycol is crucial. If the table or index does not exist, or the constraint is missing, no test of conflict is valid. A quick verification after setup:

SELECT * FROM conflict_fixture;
-- EXPECTED: one row with keycol=100, value='initial'.

If the table has extra rows or a wrong schema, reject (HOLD).

4. Author the independent expected-case manifest

Before running anything, we write down exactly what we expect in each test case. This oracle is independent of Postgres and must hold true if the system is correct. For brevity, we refer to cases by ID:

Each manifest entry spells out exactly what success looks like: number and content of returned rows, and the final table contents. This is the oracle we will match against. Any deviation is a failure.

If any manifest expectation is ambiguous or incomplete (e.g. “caller expects id = 1” without context), we mark the contract as incomplete. In that case, HOLD the test until the manifest is clarified, because we cannot judge success without a full expectation.

Preserve the failed-run artifact

For each test case above, we plan to preserve all outputs and state if it does not match expected. For example, if an INSERT returns an extra row or crashes unexpectedly, we must save the SQL output (psql stdout/stderr), the table dump, and any log.

Assertion: For every case, we either record the observed result (rows returned or error) or a blank result if none. If a case was supposed to return something and did not, or vice versa, that discrepancy is captured. If any case runs contrary to expectation and we fail to log it, we reject the evidence as incomplete (HOLD).

5. Reproduce the misleading success signal

Now we execute the critical case that originally caused confusion: a conflict with a false WHERE. In pseudo-psql commands (not actually run here), it looks like:

-- CASE C2: Conflict, WHERE false => no update
-- (This is EXPECTED behavior, per docs.)
INSERT INTO conflict_fixture (keycol, value)
VALUES (100, 'baz')
ON CONFLICT (keycol) DO UPDATE
  SET value = excluded.value
  WHERE false
RETURNING id;
-- EXPECTED (docs): no row returned, since WHERE false => skip update.
-- In psql this would print "INSERT 0 0" with no output rows.

Expected vs Observed: We anticipate no row in RETURNING. That is a valid documented outcome, not an error. We mark this case EXPECTED / NOT EXECUTED, with a note that RETURNING will be empty.

At runtime, psql would show something like:

INSERT 0 0

indicating zero rows were inserted or updated. We do not treat that as failure here, only as the outcome. But caller code should not assume “success with no data” means anything was done. This is the misleading-success: the INSERT statement succeeded (no error) yet returned no data.

6. Run the contrasting valid case

Next we show how a normal ON CONFLICT update should behave. This confirms that UPDATE+RETURNING works when its predicate is true:

-- CASE C1: Conflict, WHERE true => update
INSERT INTO conflict_fixture (keycol, value)
VALUES (100, 'beta')
ON CONFLICT (keycol) DO UPDATE
  SET value = excluded.value
  WHERE true
RETURNING id, value;
-- EXPECTED: 1 row returned (the existing row), with id and updated value 'beta'.
-- For example: id=1, value='beta'.

Here we expect the existing row (with keycol=100) to be updated to 'beta'. The RETURNING clause should output that row’s id and new value. This validates that our setup and RETURNING are working.

For context, note that other locking scenarios exist (e.g. fair queuing with SELECT FOR UPDATE SKIP LOCKED), but in this single-thread case we’re only locking the target row. Because WHERE true, the update will execute and the row is returned as expected.

Assert an explicit rejection condition

Assertion: If this “WHERE true” case does not return exactly one row, the setup is broken. For example, if it returned zero rows (as in Case C2) or more than one, we reject. A missing RETURNING row here is not allowed; it would violate even PostgreSQL’s own “insert-or-update” guarantee. (Row-locking nuance aside, an ON CONFLICT DO UPDATE with a true predicate must update and count as success.) In our testing script, we would halt and mark this as FAIL if it does not return the expected row.

7. Test failure and boundary inputs

We now check failure paths and edge cases:

• Case C3 (error injection): Attempt the duplicate insert without ON CONFLICT. This should error:

  INSERT INTO conflict_fixture (keycol, value)
VALUES (100, 'gamma');
-- Expected: UNIQUE VIOLATION error.
-- Outcome: no change in table.

Absence of ON CONFLICT should raise a duplicate key error (Postgres error code 23505). We mark this as EXPECTED / NOT EXECUTED with outcome = error. If instead it inserted or silently ignored, that’s a serious bug.

• Case Rollback (boundary): We test transactional rollback resilience. Example:

  BEGIN;
  INSERT INTO conflict_fixture (keycol, value)
  VALUES (100, 'delta')
  ON CONFLICT (keycol) DO UPDATE
    SET value = excluded.value
    WHERE false;
ROLLBACK;
-- EXPECTED: after ROLLBACK, the table returns to its prior state.
-- No row should have changed (value remains from Case C1 'beta').

This confirms that a skipped UPDATE (where false) inside a transaction leaves no lasting effect when rolled back. If a row had been updated or inserted incorrectly, the rollback test would fail. (We’ll not actually execute this, but record as NOT EXECUTED with expected outcome.)

• Optional Case (DO NOTHING): As a boundary check (not central to our scenario), note that ON CONFLICT DO NOTHING RETURNING * also returns no row on conflict, similar to a false WHERE. We could test:

  INSERT INTO conflict_fixture (keycol, value)
VALUES (100, 'epsilon')
ON CONFLICT (keycol) DO NOTHING
RETURNING id;
-- EXPECTED: no row returned (no action taken).

This behaves like DO UPDATE with a false predicate: the INSERT “succeeds” but returns nothing.

All these cases are marked EXPECTED / NOT EXECUTED in our plan. The key assertion is: our manifest covers both normal and error paths. Any boundary violation (no error when we expect one, or vice versa) is a failure to meet the contract. In particular, if an insert without ON CONFLICT does not error on duplicate, that is a broken schema/constraint; test aborted.

8. Capture a complete per-case evidence ledger

Below is the ledger of all planned cases, summarizing expected vs actual results. Since we are not running live tests here, we mark them EXPECTED / NOT EXECUTED and fill in outcomes as per documentation:

The asterisk (*) denotes the actual ID number, which is implementation-dependent.

Each line shows Expected results per our manifest (rows returned and table state). “Observed” is blank because this is a plan. The Status flags all as EXPECTED.

Detect incomplete evidence

Assertion: No case should lack a documented expectation. Here, all planned cases (NC, C1, C2, C3, RB) appear exactly once in the ledger. If a case were missing or a field was undefined (e.g. “Expected RETURNING = * ” with no detail), we would consider the evidence incomplete. Incomplete evidence is grounds to HOLD. Likewise, if an unexpected output (like multiple rows returned) were observed, we would log it and HOLD until analysts can interpret it.

9. Reconcile expected and observed state

With the tests (if executed), we would reconcile what actually happened against our manifest. This means querying the final table and comparing to the manifest’s predictions. For example:

SELECT * FROM conflict_fixture;

We expect exactly one row with keycol=100 and value='beta' (from Case C1); and one row with keycol=200, value='alpha' if Case NC ran. In other words, the final snapshot must match our independent manifest’s table state for each case. This is akin to evaluating a model against ground truth: the manifest is our “ground truth dataset,” and the actual table is the “predictions.” As the Refonte Machine Learning Model Evaluation guide puts it, we must define success before running tests and then check that outcome against that contract. If the database state deviates (say value is not ‘beta’), we have a failure.

In practice, after each test or at the end we would take a table dump:

-- Final state check
SELECT keycol, value FROM conflict_fixture ORDER BY keycol;

And compare row-for-row with the manifest’s expected values. Any mismatch (extra row, wrong value, missing key) indicates a problem. If all rows match exactly, the behavior is correct w.r.t our manifest.

10. Distinguish documented guarantees from observations

We explicitly separate what the documentation guarantees from what our acceptance criteria demand. For example, official docs reassure us that no row on RETURNING is expected when WHERE is false. Our observations (expected vs actual) should align with that guarantee. In our cases:

• Doc Guarantee: “Only rows that were successfully inserted or updated will be returned. For example, if a row was locked but not updated because an ...WHERE condition was not satisfied, the row will not be returned.”

• Our Observation (Expected): Case C2 (WHERE false) should indeed return no row, per the doc. Case C1 (WHERE true) should return exactly one row. If our evidence (once collected) matched these, we have confirmation that Postgres behaved per spec.

If we had observed something else (e.g. C1 returning no row, or C2 unexpectedly returning the row), it would conflict with documented behavior, indicating a serious bug or misunderstanding. That situation would require holding execution and perhaps filing a bug with Postgres, or at least halting development until clarified. As long as our expected vs actual remains consistent with documentation, we mark those cases as Accept (no DB bug). If we ever see a divergence, we escalate (HOLD).

Prove the repair does not widen scope

Before proposing any fix, we must ensure it doesn’t alter the semantics beyond our contract. For instance, removing the WHERE predicate entirely would make the UPDATE run on every conflict. That would widen the scope of affected rows compared to the original request. Specifically, a fix like:

-- BAD REPAIR EXAMPLE:
INSERT INTO conflict_fixture(keycol, value) VALUES (100, 'fix')
ON CONFLICT (keycol) DO UPDATE
  SET value = excluded.value
RETURNING id;

(with no WHERE false) would update the row for key=100 even when we intentionally wanted to skip it. This changes business logic, possibly undesired. We must explicitly reject any such fix if it causes unintended updates.

Assertion: Any candidate repair must be tested against case C2 to show it doesn’t update rows that were meant to be untouched. If it does (as above), then the repair “widens scope” and is unacceptable. Our test would capture this as a failure mode, and we would HOLD that repair (do not adopt it).

11. Implement a bounded repair

Since we cannot modify Postgres’s internal RETURNING behavior, we implement an application-side fix. The common solution is a one-statement fallback that selects the ID if the INSERT didn’t return it. For example:

-- BOUNDED REPAIR (UNION ALL fallback):
WITH upsert AS (
  INSERT INTO conflict_fixture(keycol, value) 
  VALUES (100, 'epsilon')
  ON CONFLICT (keycol) DO UPDATE
    SET value = excluded.value
    WHERE false
  RETURNING id
)
SELECT id FROM upsert
UNION ALL
SELECT id FROM conflict_fixture WHERE keycol = 100
LIMIT 1;
-- EXPECTED: returns the id of the existing row either from the INSERT or from the SELECT.

This does one INSERT/UPDATE attempt (which we know returns no id in C2) and then does a SELECT on the table for key=100 to fetch its id. The UNION ALL plus LIMIT 1 ensures exactly one id is returned (from INSERT if a new row was created, or from SELECT otherwise). Since both parts access the same table, it’s still atomic under Read Committed rules. Crucially, this repair does not update any additional rows; the WHERE false remains in place, so we only do the extra SELECT. This technique is recommended as a bounded fix (no new side effects).

We would run this in place of our original command. In our table of cases, we would record it as implemented repair (e.g. add Case C2_fix). In practice, we would observe it now returns the expected id. Here we mark it EXPECTED / NOT EXECUTED, but note: it meets the contract without unauthorized updates.

12. Validate recovery and safe reruns

After applying the repair, we must ensure the system can recover and tests can be safely re-run. This means two things:

• Recovery (Rollback): If the application error occurred mid-transaction (e.g. a later failure), rolling back should return the DB to its prior consistent state. Our Case RB above tests that. We would verify that after ROLLBACK the table still has just the original row (value='beta') and no partial effects. This is analogous to testing for crash safety or failure conditions in systems; for example, Refonte’s Kubernetes spread constraints playbook similarly defines desired state after simulated failures. Here we treat any rollback as a “failure scenario” and check that no extra rows or locks remain. In our synthetic lab, this just means confirming SELECT * after ROLLBACK yields exactly the pre-test state.

• Safe Reruns (Idempotence): We must ensure repeating the operation under the same initial state yields the same result (no double-insert, no ghost row). For example, running Case C1 twice in a row should not create a new row the second time. Our fixture is disposable, so for a fresh run we can DROP and recreate it. But if embedding in a script, we’d explicitly delete or reset state before retrying. The goal: none of our tests should introduce “one-time-only” changes that make rerun results differ. In practice, we’d rerun the script end-to-end and ensure the ledger outcomes are the same.

We also ensure idempotence of the repair: running the WITH upsert ... UNION SELECT query again (with the same key) should not change anything. It will either find the row or re-run the same logic without creating duplicates. This mirrors the Kubernetes spread constraints playbook’s resilience testing: after each “failure” or change, the system must still satisfy the original invariants.

Assign the evidence owner

Every piece of evidence (log, table state, return value) has an owner responsible for its integrity. For instance, the database administrator owns schema and data invariants; the application developer owns the query logic; the QA/test engineer owns the test script and manifest.

Assertion: If a test reveals a mismatch (e.g. missing RETURNING row), someone must be assigned to act. For example, if the table ends up with an unexpected row, the DBA should investigate schema issues. If the SQL returned something contrary to docs, the developer or Postgres team must check version differences. A lack of clear ownership is not acceptable (that’s an operational error). If evidence is not reviewed or assigned (e.g. a test failure is logged but nobody is told), that is a process failure, and we would HOLD until roles are clear.

13. Apply the ACCEPT / REPAIR / RERUN / HOLD matrix

Finally, we consolidate decisions for each scenario. We use an accept/repair/rerun/hold matrix like this:

• ACCEPT: If the behavior matches documentation and contract (cases NC, C1, C3), we accept the result and require no fix.

• REPAIR: If the behavior matches the doc but violates our caller’s contract (Case C2: no row returned but caller needed an ID), we repair with the fallback SELECT.

• RERUN: If the outcome may have been influenced by non-determinism or setup issues (e.g. a concurrent transaction hadn’t committed), we might rerun the test under controlled conditions. (Our lab is deterministic, so rerun is unlikely needed unless the environment was wrong.)

• HOLD: If any behavior contradicts documented guarantees (e.g. a row is returned when none should be), or if the environment is compromised, we hold for investigation. The decision gate is that only fully verified, documented-behavior results are accepted.

The matrix above reflects our per-case evidence ledger and repair plan. Every test either passes and is accepted, or is marked for repair/rerun/hold as shown.

14. Connect the practice to Refonte Learning

As a next step in formalizing skills, Refonte Learning offers a Database Administrator Essentials program that covers core concepts like indexing, transactions, and backup. The program is a 3-month part-time course (about 12–14 hours per week) focusing on practical DBA skills. It includes hands-on projects and mentorship. For example, the curriculum covers transaction management and performance tuning, topics highly relevant to issues like ON CONFLICT behavior and recovery. Interested readers might explore Refonte’s Database Administrator Essentials to broaden their operational database expertise.