UPSERT in MySQL, PostgreSQL, SQLite & MS SQL Server — A Complete Comparison

digitoolshq — Wed, 22 Apr 2026 21:28:05 +0000

One of the most searched SQL topics — and one of the most
confusing — is UPSERT: insert a row if it doesn't exist,
update it if it does.

The problem? Every database does it differently.

Here's the definitive comparison across all 4 major databases.

MySQL

INSERT INTO users (id, email) VALUES (1, 'new@x.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

Works on any duplicate key — primary or unique index.

PostgreSQL

INSERT INTO users (id, email) VALUES (1, 'new@x.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

EXCLUDED refers to the row that failed to insert.
You can also use ON CONFLICT DO NOTHING to silently skip.

SQLite

INSERT OR REPLACE INTO users (id, email)
VALUES (1, 'new@x.com');

SQLite deletes the old row and inserts a new one —
so auto-increment IDs will change. Watch out for that.

MS SQL Server

MERGE users AS target
USING (VALUES (1,'new@x.com')) AS src(id,email)
ON target.id = src.id
WHEN MATCHED THEN
UPDATE SET email = src.email
WHEN NOT MATCHED THEN
INSERT (id,email) VALUES (src.id,src.email);

Most verbose — but also the most flexible.
You can add WHEN NOT MATCHED BY SOURCE THEN DELETE
to turn it into a full sync operation.

Quick Reference Table

Database	Syntax Style	Deletes old row?	Notes
MySQL	ON DUPLICATE KEY	No	Any unique key triggers
PostgreSQL	ON CONFLICT	No	Column-specific control
SQLite	INSERT OR REPLACE	Yes	ID changes on replace
SQL Server	MERGE	No	Most flexible syntax

Bonus: Python Script to Run UPSERT Across Any DB

If you're automating data pipelines, here's a Python
snippet that detects your DB type and runs the right
UPSERT automatically:

def upsert(conn, db_type, table, data, conflict_col):
    if db_type == "mysql":
        cols = ", ".join(data.keys())
        vals = ", ".join(["%s"] * len(data))
        updates = ", ".join([f"{k}=VALUES({k})" for k in data])
        sql = f"""INSERT INTO {table} ({cols}) VALUES ({vals})
                  ON DUPLICATE KEY UPDATE {updates}"""
    elif db_type == "postgresql":
        cols = ", ".join(data.keys())
        vals = ", ".join(["%s"] * len(data))
        updates = ", ".join([f"{k}=EXCLUDED.{k}" for k in data])
        sql = f"""INSERT INTO {table} ({cols}) VALUES ({vals})
                  ON CONFLICT ({conflict_col}) DO UPDATE SET {updates}"""
    elif db_type == "sqlite":
        cols = ", ".join(data.keys())
        vals = ", ".join(["?"] * len(data))
        sql = f"INSERT OR REPLACE INTO {table} ({cols}) VALUES ({vals})"

    cursor = conn.cursor()
    cursor.execute(sql, list(data.values()))
    conn.commit()

Want the Full Reference?

I put together a complete SQL cheat pack covering 50+
queries across all 4 databases — plus 5 Python automation
scripts for common DB tasks like exporting to CSV, finding
duplicates, and auto-documenting your schema.

It's on Gumroad here: https://bit.ly/4mIC6It

Happy to answer any SQL questions in the comments!

DEV Community: digitoolshq