DevEquiv

    MySQL ON DUPLICATE KEY UPDATE → PostgreSQL ON CONFLICT

    Converting MySQL's upsert syntax to PostgreSQL's ON CONFLICT DO UPDATE, including the VALUES(col) to EXCLUDED.col rename.

    HIGH

    Quick answer

    ON CONFLICT (column_or_constraint) DO UPDATE SET col = EXCLUDED.col is the PostgreSQL equivalent of ON DUPLICATE KEY UPDATE col = VALUES(col) — but you must name the specific unique constraint the upsert targets, and VALUES(col) becomes EXCLUDED.col.

    Why it breaks

    MySQL's ON DUPLICATE KEY UPDATE reacts to a conflict on any unique key or primary key on the table, whichever one the inserted row happens to violate — it never names which one. PostgreSQL's ON CONFLICT clause always requires an explicit conflict target (a column list matching a unique constraint, or the constraint name itself) — there's no implicit "any key" behavior to fall back on.

    Real examples

    Basic upsert

    MySQL 
    INSERT INTO inventory (sku, stock)
VALUES ('SKU-1', 5)
ON DUPLICATE KEY UPDATE stock = VALUES(stock);
    PostgreSQL 
    INSERT INTO inventory (sku, stock)
VALUES ('SKU-1', 5)
ON CONFLICT (sku) DO UPDATE SET stock = EXCLUDED.stock;
    The conflict target (sku) must match an existing unique constraint; VALUES(stock) becomes EXCLUDED.stock.

    Incrementing based on the existing row (needs the table alias, not just EXCLUDED)

    MySQL 
    INSERT INTO inventory (sku, stock)
VALUES ('SKU-1', 5)
ON DUPLICATE KEY UPDATE stock = stock + VALUES(stock);
    PostgreSQL 
    INSERT INTO inventory (sku, stock)
VALUES ('SKU-1', 5)
ON CONFLICT (sku) DO UPDATE SET stock = inventory.stock + EXCLUDED.stock;
    Referencing the existing row's value (stock on its own in MySQL) requires the table name explicitly in PostgreSQL (inventory.stock) since a bare column name inside DO UPDATE SET is ambiguous between the old and new row.

    Safe migration options

    • Identify exactly which unique constraint the upsert is meant to key off — if a table has more than one unique constraint, this decision changes which rows get updated versus inserted.
    • Replace every VALUES(col) reference in the UPDATE clause with EXCLUDED.col — EXCLUDED is PostgreSQL's name for "the row that would have been inserted."
    • If the update logic needs the OLD row values too (not just the new/excluded ones), reference the table name directly in the SET clause alongside EXCLUDED — this pattern has no MySQL equivalent since VALUES() only ever exposes the proposed new values.

    PostgreSQL solution

    INSERT INTO table (...) VALUES (...) ON CONFLICT (unique_column) DO UPDATE SET col = EXCLUDED.col;

    Validation

    Before cutover

    Insert a row that conflicts on the target unique constraint and confirm the specified columns are updated to the new values (not left as the old ones, and not duplicated as a second row) — then insert a genuinely new row and confirm it inserts normally rather than hitting the DO UPDATE branch unexpectedly.

    Scan your own schema

    This page documents detector rule ON_DUPLICATE_KEY_UPDATE in the DevEquiv analyzer — paste or upload your real MySQL/MariaDB schema to find every occurrence of this (and 19 other documented issues) automatically, with a prioritized readiness report.

    Analyze a schema free

    References

    • MySQL Manual — INSERT ... ON DUPLICATE KEY UPDATE (v8.0) — checked 2026-07-30
    • PostgreSQL Manual — INSERT ... ON CONFLICT (v17) — checked 2026-07-30

    Last verified 2026-07-30.