Skip to content
HeliosDB Nano

Upsert Quick Reference

Upsert Quick Reference

Available since: v3.9.5 (2026-04-10) — native planner / executor implementation; no handler-level INSERT-catch-UPDATE workaround. Verified against: v3.19.1.

UVP

Two SQL dialects, one engine: INSERT … ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col (PostgreSQL) and INSERT … ON DUPLICATE KEY UPDATE col = VALUES(col) (MySQL) both run in the same database, on the same table, against the same rows. The MySQL translator rewrites ON DUPLICATE KEY UPDATE into the canonical PG form internally — there’s a single OnConflictAction::DoUpdate path with full EXCLUDED.col reference resolution. Migrate from MySQL to Postgres-flavour incrementally without rewriting upserts.

10 regression tests cover the full matrix: DO NOTHING, DO UPDATE, EXCLUDED references, multi-column conflicts, partial update, no-conflict insert. Implementation in src/sql/planner.rs::convert_on_conflict.

PostgreSQL Form — ON CONFLICT

DO NOTHING

Insert if not present; silently skip on conflict:

CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name  TEXT
);


INSERT INTO users (email, name) VALUES ('alice@x.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

If email = 'alice@x.com' already exists, the row is unchanged. Affected rows = 0.

DO UPDATE SET … = EXCLUDED.…

The “real” upsert. EXCLUDED refers to the proposed row that conflicted:

INSERT INTO users (email, name) VALUES ('alice@x.com', 'Alice Updated')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

Behaviour:

  • If no row with email = 'alice@x.com' → INSERT.
  • If a row exists → UPDATE with name = 'Alice Updated'.

Combining with column expressions

EXCLUDED can mix with the existing column value:

CREATE TABLE counters (
    key   TEXT PRIMARY KEY,
    count INTEGER DEFAULT 0,
    updated_at TIMESTAMP DEFAULT now()
);


INSERT INTO counters (key, count) VALUES ('signups', 1)
ON CONFLICT (key) DO UPDATE
SET count = counters.count + EXCLUDED.count,
    updated_at = now();

counters.count is the existing value, EXCLUDED.count is the proposed value.

Composite unique constraints

CREATE TABLE tags (
    user_id INT,
    tag     TEXT,
    weight  REAL DEFAULT 1.0,
    UNIQUE (user_id, tag)
);


INSERT INTO tags (user_id, tag, weight) VALUES (42, 'rust', 0.5)
ON CONFLICT (user_id, tag) DO UPDATE
SET weight = tags.weight + EXCLUDED.weight;

Bulk upsert

INSERT INTO users (email, name) VALUES
  ('alice@x.com',  'Alice'),
  ('bob@x.com',    'Bob'),
  ('carol@x.com',  'Carol')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name;

Each row is evaluated independently for conflict.

DO UPDATE with RETURNING

INSERT INTO users (email, name) VALUES ('alice@x.com', 'Alice v2')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name
RETURNING id, email, name;

Returns the row regardless of whether it was inserted or updated.

MySQL Form — ON DUPLICATE KEY UPDATE

The same operation in MySQL syntax — works in the same database against the same tables:

INSERT INTO users (email, name) VALUES ('alice@x.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

VALUES(col) in MySQL is the equivalent of EXCLUDED.col in PG. The MySQL translator rewrites this to the canonical PG form (src/sql/planner.rs, convert_on_conflict).

Counter increment, MySQL style

INSERT INTO counters (key, count) VALUES ('signups', 1)
ON DUPLICATE KEY UPDATE count = count + VALUES(count);

Composite unique key

CREATE TABLE tags (
    user_id INT,
    tag     TEXT,
    weight  REAL DEFAULT 1.0,
    UNIQUE KEY user_tag (user_id, tag)
);


INSERT INTO tags (user_id, tag, weight) VALUES (42, 'rust', 0.5)
ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight);

The UNIQUE KEY syntax (MySQL) is parsed and creates the same unique index that PG UNIQUE produces.

Mixing the Dialects

Both forms target the same unique constraints. You can issue PG-form upserts and MySQL-form upserts in the same session, against the same table, in any order:

-- Connection 1 (PG-flavour client)
INSERT INTO users (email, name) VALUES ('alice@x.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;


-- Connection 2 (MySQL-flavour client)
INSERT INTO users (email, name) VALUES ('bob@x.com', 'Bob')
ON DUPLICATE KEY UPDATE name = VALUES(name);

Both rows end up in the same table with the same constraint behaviour.

What Counts as a Conflict?

The conflict target is any unique constraint — primary key or UNIQUE index — that the proposed row violates. v3.9.4 fixed the conflict-detection path so:

  • PRIMARY KEY violations trigger ON CONFLICT (was missed pre-3.9.4 — only checked unique_indexes).
  • Composite UNIQUE (a, b) triggers when both a and b match.
  • Columns must be quoted-or-not consistently with the table definition.

For PG ON CONFLICT (col), the explicit conflict target must match a unique constraint. For MySQL ON DUPLICATE KEY UPDATE, any unique-key violation matches.

Verification

-- Idempotent insert
INSERT INTO users (email, name) VALUES ('test@x.com', 'Test')
ON CONFLICT (email) DO NOTHING;
INSERT INTO users (email, name) VALUES ('test@x.com', 'Test 2')
ON CONFLICT (email) DO NOTHING;
SELECT name FROM users WHERE email = 'test@x.com';
-- 'Test' (not 'Test 2')


-- Upsert produces the new value
INSERT INTO users (email, name) VALUES ('test@x.com', 'Test 3')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
SELECT name FROM users WHERE email = 'test@x.com';
-- 'Test 3'

Common Pitfalls

PitfallSymptomFix
ON CONFLICT (col) without UNIQUE on colParse errorAdd UNIQUE constraint or PRIMARY KEY on the column
EXCLUDED.col references base columncolumn "col" does not existQualify with EXCLUDED.col for proposed, tablename.col for existing
MySQL INSERT IGNORE not used hereErrors not silencedUse ON CONFLICT DO NOTHING or ON DUPLICATE KEY UPDATE col = col
Conflict on PRIMARY KEY missed (pre-3.9.4)Duplicate insert silently succeededUpgrade to v3.9.5+ — PK is now a real conflict target
Upsert in transaction sees stale rowsRead isolation surpriseUse FOR UPDATE (PG) or rely on default snapshot — both work
Multi-row upsert with conflicting batchOnly first conflict caughtOne conflict per row — second matching key in same INSERT does proceed correctly

Where Next

CHANGELOG references: v3.9.5 (native ON CONFLICT planner/executor + EXCLUDED resolution + 10 upsert tests), v3.9.4 (PK + composite UNIQUE in conflict detection), v3.9.0 (LAST_INSERT_ID for upsert returning), v3.19.1 (current verified release).