---
title: Upsert Quick Reference
description: PostgreSQL ON CONFLICT and MySQL ON DUPLICATE KEY UPDATE in one engine
build: default (no feature flag required)
---

# Upsert Quick Reference


---

## 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:

```sql
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:

```sql
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:

```sql
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

```sql
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

```sql
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`

```sql
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:

```sql
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

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

### Composite unique key

```sql
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:

```sql
-- 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. Conflict detection covers:

- `PRIMARY KEY` violations trigger ON CONFLICT (older releases checked only `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

```sql
-- 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

| Pitfall | Symptom | Fix |
|---|---|---|
| `ON CONFLICT (col)` without UNIQUE on `col` | Parse error | Add `UNIQUE` constraint or `PRIMARY KEY` on the column |
| `EXCLUDED.col` references base column | `column "col" does not exist` | Qualify with `EXCLUDED.col` for proposed, `tablename.col` for existing |
| MySQL `INSERT IGNORE` not used here | Errors not silenced | Use `ON CONFLICT DO NOTHING` or `ON DUPLICATE KEY UPDATE col = col` |
| Conflict on PRIMARY KEY missed (older releases) | Duplicate insert silently succeeded | Upgrade to the current release: PK is a real conflict target |
| Upsert in transaction sees stale rows | Read isolation surprise | Use `FOR UPDATE` (PG) or rely on default snapshot — both work |
| Multi-row upsert with conflicting batch | Only first conflict caught | One conflict per row — second matching key in same INSERT does proceed correctly |

---

## Where Next

- [WINDOW_FUNCTIONS_QUICKREF](https://www.heliosdb.com/docs/nano/guides/quickstart/window_functions_quickref/) — common partner of upserts in event-stream pipelines.
- [BAAS_REST_API_TUTORIAL](https://www.heliosdb.com/docs/nano/guides/tutorials/baas_rest_api_tutorial/) — `Prefer: resolution=merge-duplicates` headers map to upsert behaviour.
- [EXPLAIN_AND_OPTIMIZATION_TUTORIAL](https://www.heliosdb.com/docs/nano/guides/tutorials/explain_and_optimization_tutorial/) — confirm your conflict target hits an index.

---

*See the [changelog](https://www.heliosdb.com/docs/nano/releases/changelog/) for the release history of upsert support.*