WHERE Clause in ON CONFLICT UPSERT Ignored: Causes and Fixes

Understanding the Role of WHERE in ON CONFLICT Conflict Targets

Issue Overview

The core issue arises when developers attempt to use a WHERE clause within the ON CONFLICT conflict target (the syntax between ON CONFLICT and DO UPDATE/DO NOTHING) to conditionally control whether an upsert operation occurs. For example, a user might write:

INSERT INTO test (u, n) 
VALUES (0, 1) 
ON CONFLICT (u) WHERE n IS NULL 
DO UPDATE SET u = excluded.u, n = excluded.n;

The expectation is that the DO UPDATE action will only execute if the conflicting row (the row that triggered the UNIQUE constraint violation) satisfies n IS NULL. However, in SQLite, the WHERE clause in the conflict target is not evaluated at runtime to filter individual rows. Instead, its purpose is to infer which unique index or constraint should be used to detect conflicts. If the conflict target’s WHERE clause does not match a partial index’s predicate, SQLite ignores it entirely.

This leads to unexpected behavior:

1. The DO UPDATE action executes even when the conflicting row does not satisfy n IS NULL.
2. If no partial index exists that matches the conflict target’s WHERE clause, SQLite defaults to using the closest matching unique constraint (e.g., a non-partial UNIQUE constraint on u), rendering the WHERE clause inert.
3. Subsequent insertions may trigger constraint violations because the upsert logic did not apply the intended conditional update.

The confusion stems from conflating two distinct concepts:

• Conflict Target Selection: Identifying which unique constraint/index is violated.
• Conditional Upsert Execution: Deciding whether to perform DO UPDATE based on runtime values.

SQLite’s documentation historically lacked clarity on this distinction, leading developers to misinterpret the role of the WHERE clause in conflict targets.

Misalignment Between Conflict Target Semantics and Partial Index Requirements

Possible Causes

1. Misunderstanding Conflict Target Syntax
   The WHERE clause in the conflict target is designed to identify a partial index, not to filter rows. For example, if a partial index exists as:

   CREATE UNIQUE INDEX test_partial ON test(u) WHERE n IS NULL;
   

   Then ON CONFLICT (u) WHERE n IS NULL would bind to this index. However, if no such index exists, SQLite ignores the WHERE clause and uses the next best match (e.g., a non-partial UNIQUE constraint on u).

2. Absence of Matching Partial Index
   If the table lacks a partial index whose columns and predicate match the conflict target’s (u) WHERE n IS NULL, SQLite cannot enforce the intended conditional conflict resolution. It defaults to the non-partial UNIQUE constraint on u, causing the DO UPDATE to execute unconditionally.

3. Documentation Ambiguity
   SQLite’s documentation prior to version 3.41.2 did not explicitly state that the conflict target’s WHERE clause is used solely for partial index inference. Developers assumed it acted as a runtime filter for upsert eligibility.

4. PostgreSQL Compatibility Misconceptions
   While SQLite’s UPSERT syntax is inspired by PostgreSQL, the two systems differ in handling conflict targets. PostgreSQL requires explicit partial index references via ON CONSTRAINT or precise index inference. SQLite’s laxer inference rules can lead to unintended index selection.

5. Overloading the Conflict Target Clause
   Developers often attempt to use the conflict target’s WHERE clause to implement business logic (e.g., "only update if the existing row meets a condition"). This is not supported; such logic belongs in the DO UPDATE’s WHERE clause.

Resolving Conditional Upsert Execution and Index Binding

Troubleshooting Steps, Solutions & Fixes

Step 1: Correctly Structure the UPSERT Statement

Problem: The WHERE clause in the conflict target is not filtering rows.
Solution: Move the condition to the DO UPDATE clause.

Example Fix:

INSERT INTO test (u, n) 
VALUES (0, 1) 
ON CONFLICT (u) 
DO UPDATE SET u = excluded.u, n = excluded.n 
WHERE n IS NULL;  -- Condition moved here

Explanation:

• The ON CONFLICT (u) binds to the UNIQUE constraint on u.
• The WHERE n IS NULL in DO UPDATE filters which conflicting rows are updated.
• If the existing row has n IS NULL, the update proceeds; otherwise, the update is skipped, and the INSERT fails with a constraint error (unless another ON CONFLICT clause exists).

Step 2: Define a Partial Index for Conflict Target Binding

Problem: The conflict target’s WHERE clause is ignored because no matching partial index exists.
Solution: Create a partial index that matches the conflict target’s columns and predicate.

Example Fix:

CREATE TABLE test (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  u INTEGER UNIQUE,  -- Non-partial UNIQUE constraint
  n INTEGER
);

-- Add a partial index for conditional conflict resolution
CREATE UNIQUE INDEX test_partial ON test(u) WHERE n IS NULL;

-- Use the partial index in the conflict target
INSERT INTO test (u, n) 
VALUES (0, 1) 
ON CONFLICT (u) WHERE n IS NULL  -- Binds to test_partial
DO UPDATE SET u = excluded.u, n = excluded.n;

Explanation:

• The WHERE n IS NULL in the conflict target now references the test_partial index.
• The DO UPDATE only executes if the conflict arises from this partial index (i.e., the existing row has u=0 and n IS NULL).
• Conflicts on the non-partial UNIQUE constraint on u will trigger a different resolution path (e.g., DO NOTHING if specified).

Step 3: Use Multiple ON CONFLICT Clauses for Tiered Resolution

Problem: A single conflict target cannot handle multiple conditions.
Solution: Chain ON CONFLICT clauses with different conflict targets.

Example Fix:

INSERT INTO test (u, n) 
VALUES (0, 1) 
ON CONFLICT (u) WHERE n IS NULL 
  DO UPDATE SET u = excluded.u, n = excluded.n 
ON CONFLICT (u) 
  DO NOTHING;  -- Fallback for other conflicts on 'u'

Explanation:

• The first ON CONFLICT clause handles conflicts on the partial index (u where n IS NULL).
• The second ON CONFLICT clause acts as a catch-all for other conflicts on u (e.g., rows where n is not NULL).
• Note: SQLite evaluates ON CONFLICT clauses in the order they are written.

Step 4: Validate Index Selection with EXPLAIN QUERY PLAN

Problem: Uncertainty about which index is used for conflict resolution.
Solution: Use EXPLAIN QUERY PLAN to debug index binding.

Example:

EXPLAIN QUERY PLAN
INSERT INTO test (u, n) 
VALUES (0, 1) 
ON CONFLICT (u) WHERE n IS NULL 
DO UPDATE SET u = excluded.u, n = excluded.n;

Output Analysis:

• Look for lines mentioning USING INDEX test_partial to confirm the partial index is used.
• If the output references the non-partial index (test.u), the conflict target’s WHERE clause is being ignored.

Step 5: Use Explicit Constraint Names (If Available)

Problem: Ambiguity in conflict target binding.
Solution: Name constraints explicitly and reference them via ON CONSTRAINT.

Example Fix:

CREATE TABLE test (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  u INTEGER,
  n INTEGER,
  CONSTRAINT u_unique UNIQUE (u)  -- Named constraint
);

INSERT INTO test (u, n) 
VALUES (0, 1) 
ON CONFLICT ON CONSTRAINT u_unique 
DO UPDATE SET n = excluded.n 
WHERE n IS NULL;

Explanation:

• ON CONSTRAINT u_unique explicitly binds to the u_unique constraint.
• The WHERE n IS NULL in DO UPDATE filters which conflicts trigger updates.

Step 6: Update SQLite and Review Documentation

Problem: Outdated SQLite versions or documentation misunderstandings.
Solution:

1. Upgrade to SQLite 3.41.2 or later, where partial index handling has been refined.
2. Study the official UPSERT documentation, noting that:
   • The conflict target’s WHERE clause is for index inference, not row filtering.
   • Partial indexes must match the conflict target’s columns and predicate.

Final Example Workflow

1. Define a Partial Index:

   CREATE TABLE orders (
     order_id INTEGER PRIMARY KEY,
     customer_id INTEGER,
     status TEXT,
     UNIQUE (customer_id)  -- Non-partial unique constraint
   );
   
   CREATE UNIQUE INDEX orders_active_customer 
     ON orders(customer_id) WHERE status = 'active';
   

2. Insert with Tiered Conflict Resolution:

   INSERT INTO orders (customer_id, status) 
   VALUES (100, 'pending') 
   ON CONFLICT (customer_id) WHERE status = 'active' 
     DO UPDATE SET status = excluded.status 
   ON CONFLICT (customer_id) 
     DO NOTHING;
   

3. Outcome:

   • If a row with customer_id=100 and status='active' exists, it is updated to status='pending'.
   • If a row with customer_id=100 and status!='active' exists, the DO NOTHING clause skips the update.

By following these steps, developers can align their UPSERT logic with SQLite’s conflict resolution mechanics, ensuring predictable and correct behavior.