SQL UPSERT: INSERT or UPDATE (ON CONFLICT / MERGE)

One of the most common patterns in application development is this: you receive a record and need to save it. If the record does not exist yet, insert it. If it already exists, update it with the new values. This "insert or update" logic is so prevalent that it has its own name: UPSERT.

Without a dedicated SQL UPSERT mechanism, developers must write clumsy multi-step logic: query the database to check if the row exists, then conditionally run either an INSERT or an UPDATE. This approach is verbose, slow (two round trips instead of one), and vulnerable to race conditions in concurrent environments where two processes might both check, both find the row missing, and both try to insert, causing a duplicate key error.

Modern databases solve this problem with built-in upsert syntax. PostgreSQL uses INSERT ... ON CONFLICT, MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, and SQL Server and Oracle use the MERGE statement. This guide covers all three approaches with practical examples, explains the differences, and shows you how to handle upserts safely and efficiently regardless of your database.

Why UPSERT Matters

Consider a product catalog that receives daily price feeds from a supplier. Each feed contains products that may or may not already exist in your database. Without upsert, your code looks something like this:

# Pseudocode: the naive approach
for product in supplier_feed:
    existing = db.query("SELECT 1 FROM products WHERE sku = ?", product.sku)
    if existing:
        db.execute("UPDATE products SET price = ? WHERE sku = ?", product.price, product.sku)
    else:
        db.execute("INSERT INTO products (sku, name, price) VALUES (?, ?, ?)",
                   product.sku, product.name, product.price)

This approach has three problems:

1. Two queries per row. Each product requires a SELECT plus either an INSERT or UPDATE.
2. Race conditions. Between the SELECT and the INSERT, another process might insert the same row, causing a duplicate key error.
3. Verbose code. The conditional logic clutters your application layer with database-level concerns.

An upsert reduces this to a single, atomic statement per row that handles both cases.

The Sample Schema

All examples use this table:

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    category      VARCHAR(50),
    price         DECIMAL(10,2) NOT NULL,
    stock_qty     INT DEFAULT 0,
    last_updated  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products VALUES (101, 'Wireless Mouse',      'Electronics', 25.99, 150, '2024-06-01 10:00:00');
INSERT INTO products VALUES (102, 'USB-C Cable',          'Electronics',  9.99,  300, '2024-06-01 10:00:00');
INSERT INTO products VALUES (103, 'Notebook A5',          'Stationery',   4.50,  500, '2024-06-01 10:00:00');
INSERT INTO products VALUES (104, 'Mechanical Keyboard',  'Electronics', 89.99,   45, '2024-06-01 10:00:00');

We will also use a table with a unique constraint on a non-primary column:

CREATE TABLE customers (
    customer_id   SERIAL PRIMARY KEY,       -- auto-generated
    email         VARCHAR(100) UNIQUE NOT NULL,
    full_name     VARCHAR(100),
    city          VARCHAR(50),
    signup_date   DATE DEFAULT CURRENT_DATE
);

INSERT INTO customers (email, full_name, city) VALUES ('alice@example.com', 'Alice Martin', 'New York');
INSERT INTO customers (email, full_name, city) VALUES ('bob@example.com', 'Bob Jones', 'Chicago');

PostgreSQL: INSERT ... ON CONFLICT

PostgreSQL introduced INSERT ... ON CONFLICT in version 9.5. It is often called the "upsert clause" and provides fine-grained control over what happens when an insert would violate a unique constraint.

Basic Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3)
ON CONFLICT (conflict_column)
DO UPDATE SET
    column2 = EXCLUDED.column2,
    column3 = EXCLUDED.column3;

The key elements:

• ON CONFLICT (conflict_column) specifies which unique constraint or column to watch. When an insert would violate this constraint, the conflict action triggers.
• DO UPDATE SET defines what to update when a conflict occurs.
• EXCLUDED is a special table reference that holds the values you attempted to insert. It lets you reference the "incoming" data in your update expressions.

Example: Insert a New Product or Update If It Exists

Product 101 already exists. Product 105 does not. Let's handle both in a single pattern:

-- Product 101 exists: this will UPDATE
INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse', 'Electronics', 27.99, 200)
ON CONFLICT (product_id)
DO UPDATE SET
    price        = EXCLUDED.price,
    stock_qty    = EXCLUDED.stock_qty,
    last_updated = CURRENT_TIMESTAMP;

To verify:

SELECT product_id, product_name, price, stock_qty, last_updated
FROM products WHERE product_id = 101;

product_id	product_name	price	stock_qty	last_updated
101	Wireless Mouse	27.99	200	2026-02-25 10:07:44

The price changed from 25.99 to 27.99, stock_qty from 150 to 200, and last_updated was refreshed.

-- Product 105 does not exist: this will INSERT
INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (105, 'Desk Lamp', 'Furniture', 34.50, 75)
ON CONFLICT (product_id)
DO UPDATE SET
    price        = EXCLUDED.price,
    stock_qty    = EXCLUDED.stock_qty,
    last_updated = CURRENT_TIMESTAMP;

SELECT product_id, product_name, price, stock_qty
FROM products WHERE product_id = 105;

product_id	product_name	price	stock_qty
105	Desk Lamp	34.50	75

No conflict occurred, so the row was simply inserted. The DO UPDATE clause was ignored.

ON CONFLICT DO NOTHING

If you want to silently skip rows that would cause a conflict without updating anything:

INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse v2', 'Electronics', 29.99, 250)
ON CONFLICT (product_id)
DO NOTHING;

Product 101 already exists, so nothing happens. No error, no update, no new row. The statement completes silently.

SELECT product_id, product_name, price FROM products WHERE product_id = 101;

product_id	product_name	price
101	Wireless Mouse	27.99

The values remain unchanged because DO NOTHING was specified.

tip

DO NOTHING is useful for idempotent inserts where you want to load data that might partially overlap with existing data. If the row exists, skip it. If it does not, insert it. No errors either way.

Conflict on a Unique Constraint (Non-Primary Key)

You can target any unique constraint, not just the primary key:

-- Email is unique. Upsert based on email.
INSERT INTO customers (email, full_name, city)
VALUES ('alice@example.com', 'Alice M. Martin', 'San Francisco')
ON CONFLICT (email)
DO UPDATE SET
    full_name = EXCLUDED.full_name,
    city      = EXCLUDED.city;

SELECT * FROM customers WHERE email = 'alice@example.com';

customer_id	email	full_name	city	signup_date
1	alice@example.com	Alice M. Martin	San Francisco	2026-02-25

The customer_id and signup_date remained unchanged (they were not in the DO UPDATE SET clause), but full_name and city were updated.

Using a Named Constraint

Instead of specifying the column, you can reference the constraint by name:

-- Assuming the constraint is named "products_pkey"
INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse', 'Electronics', 29.99, 180)
ON CONFLICT ON CONSTRAINT products_pkey
DO UPDATE SET
    price     = EXCLUDED.price,
    stock_qty = EXCLUDED.stock_qty;

This is useful when the conflict target involves a composite unique constraint where listing all columns would be verbose.

Conditional Updates with WHERE

PostgreSQL lets you add a WHERE clause to the DO UPDATE to conditionally decide whether the update should actually happen:

-- Only update the price if the new price is higher (never decrease)
INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse', 'Electronics', 22.00, 300)
ON CONFLICT (product_id)
DO UPDATE SET
    price        = EXCLUDED.price,
    stock_qty    = EXCLUDED.stock_qty,
    last_updated = CURRENT_TIMESTAMP
WHERE EXCLUDED.price > products.price;

The incoming price (22.00) is lower than the current price (27.99), so the WHERE condition is FALSE and the update is skipped. The row remains unchanged.

SELECT product_id, price, stock_qty FROM products WHERE product_id = 101;

product_id	price	stock_qty
101	27.99	200

info

Inside the WHERE clause of DO UPDATE, you can reference:

• EXCLUDED.column for the values you attempted to insert (the incoming data).
• table_name.column (or the alias) for the values currently stored in the table.

This lets you build sophisticated conflict resolution logic like "only update if the incoming timestamp is newer" or "only update if the incoming price is different."

Bulk Upsert with Multi-Row Values

You can combine multi-row VALUES with ON CONFLICT to upsert many rows at once:

INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES
    (101, 'Wireless Mouse',      'Electronics', 28.99, 180),
    (102, 'USB-C Cable',          'Electronics', 10.99, 400),
    (105, 'Desk Lamp',            'Furniture',   36.00,  60),
    (106, 'Pen Pack',             'Stationery',   2.99, 800)
ON CONFLICT (product_id)
DO UPDATE SET
    price        = EXCLUDED.price,
    stock_qty    = EXCLUDED.stock_qty,
    last_updated = CURRENT_TIMESTAMP;

Products 101 and 102 are updated with new prices and stock quantities. Products 105 and 106 are inserted as new rows. All four operations happen in a single, atomic statement.

SELECT product_id, product_name, price, stock_qty FROM products ORDER BY product_id;

product_id	product_name	price	stock_qty
101	Wireless Mouse	28.99	180
102	USB-C Cable	10.99	400
103	Notebook A5	4.50	500
104	Mechanical Keyboard	89.99	45
105	Desk Lamp	36.00	60
106	Pen Pack	2.99	800

Using RETURNING with ON CONFLICT

PostgreSQL's RETURNING clause works with upserts, letting you see exactly what happened:

INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (107, 'Monitor Arm', 'Furniture', 74.50, 30)
ON CONFLICT (product_id)
DO UPDATE SET
    price     = EXCLUDED.price,
    stock_qty = EXCLUDED.stock_qty
RETURNING product_id, product_name, price, stock_qty;

product_id	product_name	price	stock_qty
107	Monitor Arm	74.50	30

This is invaluable for application code that needs to know the final state of the row after the upsert, whether it was newly inserted or updated.

MySQL: INSERT ... ON DUPLICATE KEY UPDATE

MySQL's upsert syntax predates PostgreSQL's and uses a different keyword, but the concept is the same. When an INSERT would violate a primary key or unique index, the ON DUPLICATE KEY UPDATE clause kicks in.

Basic Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3)
ON DUPLICATE KEY UPDATE
    column2 = VALUES(column2),
    column3 = VALUES(column3);

warning

The VALUES() function in ON DUPLICATE KEY UPDATE was deprecated in MySQL 8.0.20. The new syntax uses aliases:

-- MySQL 8.0.20+ preferred syntax
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3) AS new_row
ON DUPLICATE KEY UPDATE
    column2 = new_row.column2,
    column3 = new_row.column3;

The older VALUES() syntax still works but may be removed in future versions. This guide shows both forms.

Example: Basic Upsert

-- MySQL 8.0.19 and earlier
INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse', 'Electronics', 29.99, 250)
ON DUPLICATE KEY UPDATE
    price     = VALUES(price),
    stock_qty = VALUES(stock_qty),
    last_updated = CURRENT_TIMESTAMP;

-- MySQL 8.0.20+
INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse', 'Electronics', 29.99, 250) AS new_values
ON DUPLICATE KEY UPDATE
    price     = new_values.price,
    stock_qty = new_values.stock_qty,
    last_updated = CURRENT_TIMESTAMP;

SELECT product_id, product_name, price, stock_qty FROM products WHERE product_id = 101;

product_id	product_name	price	stock_qty
101	Wireless Mouse	29.99	250

MySQL Multi-Row Upsert

INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES
    (101, 'Wireless Mouse',  'Electronics', 28.99, 180),
    (105, 'Desk Lamp',       'Furniture',   36.00,  60),
    (106, 'Pen Pack',        'Stationery',   2.99, 800)
ON DUPLICATE KEY UPDATE
    price     = VALUES(price),
    stock_qty = VALUES(stock_qty),
    last_updated = CURRENT_TIMESTAMP;

Product 101 is updated. Products 105 and 106 are inserted. One statement, three rows handled.

Key Differences from PostgreSQL

Feature	PostgreSQL ON CONFLICT	MySQL ON DUPLICATE KEY UPDATE
Conflict target specification	Explicit: ON CONFLICT (column)	Implicit: triggers on any PK or unique index violation
Referencing incoming values	EXCLUDED.column	VALUES(column) or row alias (8.0.20+)
DO NOTHING equivalent	ON CONFLICT DO NOTHING	INSERT IGNORE INTO
Conditional update (WHERE)	Supported	Not supported in the upsert clause
Conflict on specific constraint	Supported (ON CONSTRAINT name)	Not supported (matches any unique violation)
RETURNING clause	Supported	Not supported

info

MySQL's implicit conflict detection (matching any primary key or unique index) can be a double-edged sword. If your table has multiple unique constraints, the upsert may trigger on a constraint you did not intend, leading to unexpected behavior. PostgreSQL's explicit ON CONFLICT (column) gives you precise control over which constraint to match.

MySQL INSERT IGNORE

The equivalent of PostgreSQL's DO NOTHING:

INSERT IGNORE INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse v2', 'Electronics', 99.99, 999);

The row exists, so the insert is silently skipped. No error, no update.

warning

INSERT IGNORE suppresses all errors, not just duplicate key violations. This includes data truncation warnings, foreign key violations, and other constraint errors. Use it cautiously, as it can mask legitimate problems in your data.

SQL Server and Oracle: The MERGE Statement

The MERGE statement is the SQL standard's approach to upsert operations (defined in SQL:2003). It provides the most explicit and flexible syntax, allowing you to define separate actions for matching and non-matching rows.

SQL Server has supported MERGE since version 2008. Oracle since version 10g.

Basic Syntax

MERGE INTO target_table AS target
USING source_table_or_values AS source
ON target.key_column = source.key_column
WHEN MATCHED THEN
    UPDATE SET
        target.column1 = source.column1,
        target.column2 = source.column2
WHEN NOT MATCHED THEN
    INSERT (column1, column2, column3)
    VALUES (source.column1, source.column2, source.column3);

The key elements:

• MERGE INTO specifies the target table you want to insert into or update.
• USING provides the source data. This can be another table, a subquery, or (in SQL Server) a VALUES clause.
• ON defines the matching condition, similar to a join condition.
• WHEN MATCHED defines what happens when a matching row is found (update).
• WHEN NOT MATCHED defines what happens when no matching row exists (insert).

SQL Server Example: Single Row Upsert

MERGE INTO products AS target
USING (VALUES (101, 'Wireless Mouse', 'Electronics', 29.99, 250))
    AS source (product_id, product_name, category, price, stock_qty)
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET
        target.price        = source.price,
        target.stock_qty    = source.stock_qty,
        target.last_updated = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, category, price, stock_qty)
    VALUES (source.product_id, source.product_name, source.category,
            source.price, source.stock_qty);

warning

SQL Server requires a semicolon at the end of every MERGE statement. Omitting it causes a syntax error. This is one of the rare cases where SQL Server strictly enforces the semicolon.

-- This will fail:
MERGE INTO products AS target
USING ...
WHEN MATCHED THEN ...
WHEN NOT MATCHED THEN ...
-- ERROR: missing semicolon

-- Correct:
MERGE INTO products AS target
USING ...
WHEN MATCHED THEN ...
WHEN NOT MATCHED THEN ...;   -- Semicolon required

SQL Server Example: Bulk Upsert from Another Table

MERGE truly shines when the source is a table or query, not just a single row:

-- A staging table with fresh data from a supplier
CREATE TABLE product_updates (
    product_id    INT,
    product_name  VARCHAR(100),
    category      VARCHAR(50),
    price         DECIMAL(10,2),
    stock_qty     INT
);

INSERT INTO product_updates VALUES (101, 'Wireless Mouse',   'Electronics', 28.99, 300);
INSERT INTO product_updates VALUES (102, 'USB-C Cable',       'Electronics', 10.49, 450);
INSERT INTO product_updates VALUES (108, 'Webcam HD',         'Electronics', 49.99, 120);
INSERT INTO product_updates VALUES (109, 'Desk Organizer',    'Furniture',   19.99,  80);

MERGE INTO products AS target
USING product_updates AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET
        target.product_name = source.product_name,
        target.price        = source.price,
        target.stock_qty    = source.stock_qty,
        target.last_updated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (product_id, product_name, category, price, stock_qty)
    VALUES (source.product_id, source.product_name, source.category,
            source.price, source.stock_qty);

After execution:

product_id	product_name	price	stock_qty	Action
101	Wireless Mouse	28.99	300	Updated
102	USB-C Cable	10.49	450	Updated
103	Notebook A5	4.50	500	Unchanged
104	Mechanical Keyboard	89.99	45	Unchanged
108	Webcam HD	49.99	120	Inserted
109	Desk Organizer	19.99	80	Inserted

Products 101 and 102 were updated. Products 108 and 109 were inserted. Products 103 and 104 were not in the source, so they were left alone.

note

This statement won’t work in SQLite as written because:

• ❌ SQLite does not support MERGE
• ❌ SQLite does not support GETDATE()

WHEN NOT MATCHED BY SOURCE (SQL Server Only)

SQL Server extends the standard MERGE with a third clause that handles rows in the target that have no match in the source. This lets you delete or update "orphaned" target rows:

MERGE INTO products AS target
USING product_updates AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET
        target.price     = source.price,
        target.stock_qty = source.stock_qty
WHEN NOT MATCHED BY TARGET THEN
    INSERT (product_id, product_name, category, price, stock_qty)
    VALUES (source.product_id, source.product_name, source.category,
            source.price, source.stock_qty)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

This performs a full sync: rows in both tables are updated, rows only in the source are inserted, and rows only in the target are deleted. Use this with extreme caution as it can remove data you did not intend to delete.

danger

The WHEN NOT MATCHED BY SOURCE THEN DELETE clause will remove every row in the target table that does not appear in the source. If your source is a partial feed (e.g., only electronics products), you will accidentally delete all non-electronics products from the target.

Always add a condition if the source is not a complete dataset:

WHEN NOT MATCHED BY SOURCE AND target.category = 'Electronics' THEN
    DELETE;

Oracle MERGE Example

Oracle's MERGE syntax is very similar to SQL Server's but does not support the WHEN NOT MATCHED BY SOURCE clause:

MERGE INTO products target
USING product_updates source
ON (target.product_id = source.product_id)
WHEN MATCHED THEN
    UPDATE SET
        target.product_name = source.product_name,
        target.price        = source.price,
        target.stock_qty    = source.stock_qty,
        target.last_updated = SYSTIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, category, price, stock_qty)
    VALUES (source.product_id, source.product_name, source.category,
            source.price, source.stock_qty);

Note that Oracle requires the ON condition to be wrapped in parentheses and does not use the AS keyword for aliases.

Adding Conditions to MATCHED Clauses

Both SQL Server and Oracle allow AND conditions on the WHEN clauses:

MERGE INTO products AS target
USING product_updates AS source
ON target.product_id = source.product_id
WHEN MATCHED AND source.price <> target.price THEN
    UPDATE SET
        target.price        = source.price,
        target.last_updated = GETDATE()
WHEN MATCHED AND source.price = target.price THEN
    UPDATE SET
        target.stock_qty    = source.stock_qty
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, category, price, stock_qty)
    VALUES (source.product_id, source.product_name, source.category,
            source.price, source.stock_qty);

This creates different update behavior depending on whether the price changed. If only stock changed, update stock only. If price changed, update price and the timestamp.

Cross-Database Comparison

Here is the same logical upsert written in all three dialects for easy reference:

PostgreSQL

INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse', 'Electronics', 29.99, 250)
ON CONFLICT (product_id)
DO UPDATE SET
    price        = EXCLUDED.price,
    stock_qty    = EXCLUDED.stock_qty,
    last_updated = CURRENT_TIMESTAMP;

MySQL

INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse', 'Electronics', 29.99, 250) AS new_row
ON DUPLICATE KEY UPDATE
    price        = new_row.price,
    stock_qty    = new_row.stock_qty,
    last_updated = CURRENT_TIMESTAMP;

SQL Server

MERGE INTO products AS target
USING (VALUES (101, 'Wireless Mouse', 'Electronics', 29.99, 250))
    AS source (product_id, product_name, category, price, stock_qty)
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET
        target.price        = source.price,
        target.stock_qty    = source.stock_qty,
        target.last_updated = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, category, price, stock_qty)
    VALUES (source.product_id, source.product_name, source.category,
            source.price, source.stock_qty);

Feature Matrix

Feature	PostgreSQL ON CONFLICT	MySQL ON DUPLICATE KEY	MERGE (SQL Server/Oracle)
Single-row upsert	Yes	Yes	Yes (verbose)
Multi-row upsert	Yes (multi-row VALUES)	Yes (multi-row VALUES)	Yes (USING source table/query)
Skip on conflict (do nothing)	DO NOTHING	INSERT IGNORE	Omit WHEN NOT MATCHED
Conditional update	WHERE in DO UPDATE	No	AND condition on WHEN MATCHED
Explicit conflict target	Yes (ON CONFLICT (col))	No (implicit, any unique)	Yes (ON clause)
Delete unmatched target rows	No	No	Yes (WHEN NOT MATCHED BY SOURCE)
RETURNING / OUTPUT	Yes	No	Yes (SQL Server OUTPUT)
Referencing incoming values	EXCLUDED keyword	VALUES() or row alias	source alias
SQLite support	Yes (3.24+)	No	No

SQLite: INSERT ... ON CONFLICT

SQLite supports PostgreSQL-style ON CONFLICT syntax since version 3.24.0 (2018):

INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse', 'Electronics', 29.99, 250)
ON CONFLICT (product_id)
DO UPDATE SET
    price     = excluded.price,
    stock_qty = excluded.stock_qty;

SQLite also supports the older INSERT OR REPLACE syntax, which deletes the conflicting row and inserts the new one:

INSERT OR REPLACE INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse', 'Electronics', 29.99, 250);

warning

INSERT OR REPLACE is not a true upsert. It performs a DELETE followed by an INSERT. This means:

• Columns not specified in the INSERT lose their values (reset to defaults or NULL).
• The row gets a new rowid internally.
• Any ON DELETE triggers fire, which may cascade to other tables.
• Foreign key references to the deleted row break.

Prefer ON CONFLICT DO UPDATE for true upsert behavior in SQLite.

Practical Patterns

Pattern 1: Increment a Counter on Conflict

Track page views where you want to insert a new page or increment its view count:

CREATE TABLE page_views (
    page_url    VARCHAR(500) PRIMARY KEY,
    view_count  INT DEFAULT 0,
    last_viewed TIMESTAMP
);

-- PostgreSQL
INSERT INTO page_views (page_url, view_count, last_viewed)
VALUES ('/blog/sql-upsert', 1, CURRENT_TIMESTAMP)
ON CONFLICT (page_url)
DO UPDATE SET
    view_count  = page_views.view_count + 1,
    last_viewed = CURRENT_TIMESTAMP;

The first time this runs, a new row is inserted with view_count = 1. Every subsequent execution increments the counter by 1. The expression page_views.view_count + 1 references the current value in the table and adds 1.

Pattern 2: Only Update If Data Actually Changed

Avoid unnecessary writes (and trigger executions) by checking whether values differ:

-- PostgreSQL
INSERT INTO products (product_id, product_name, category, price, stock_qty)
VALUES (101, 'Wireless Mouse', 'Electronics', 28.99, 180)
ON CONFLICT (product_id)
DO UPDATE SET
    price        = EXCLUDED.price,
    stock_qty    = EXCLUDED.stock_qty,
    last_updated = CURRENT_TIMESTAMP
WHERE products.price     <> EXCLUDED.price
   OR products.stock_qty <> EXCLUDED.stock_qty;

If the incoming values are identical to the stored values, the WHERE clause prevents any update. The last_updated timestamp stays unchanged, correctly reflecting that no actual data change occurred.

Pattern 3: Syncing Data from a Staging Table

A common ETL pattern: load raw data into a staging table, then upsert into the production table.

-- PostgreSQL
INSERT INTO products (product_id, product_name, category, price, stock_qty)
SELECT product_id, product_name, category, price, stock_qty
FROM staging_products
ON CONFLICT (product_id)
DO UPDATE SET
    product_name = EXCLUDED.product_name,
    category     = EXCLUDED.category,
    price        = EXCLUDED.price,
    stock_qty    = EXCLUDED.stock_qty,
    last_updated = CURRENT_TIMESTAMP;

-- SQL Server
MERGE INTO products AS target
USING staging_products AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET
        target.product_name = source.product_name,
        target.category     = source.category,
        target.price        = source.price,
        target.stock_qty    = source.stock_qty,
        target.last_updated = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, category, price, stock_qty)
    VALUES (source.product_id, source.product_name, source.category,
            source.price, source.stock_qty);

Pattern 4: Composite Key Conflict

When the unique constraint spans multiple columns:

CREATE TABLE inventory (
    warehouse_id  INT,
    product_id    INT,
    quantity      INT,
    last_checked  TIMESTAMP,
    PRIMARY KEY (warehouse_id, product_id)
);

-- PostgreSQL
INSERT INTO inventory (warehouse_id, product_id, quantity, last_checked)
VALUES (1, 101, 50, CURRENT_TIMESTAMP)
ON CONFLICT (warehouse_id, product_id)
DO UPDATE SET
    quantity     = EXCLUDED.quantity,
    last_checked = CURRENT_TIMESTAMP;

The conflict target lists both columns of the composite primary key.

Common Mistakes

Mistake 1: Wrong Conflict Target

Specifying a column that is not part of any unique constraint causes an error:

Wrong:

INSERT INTO products (product_id, product_name, category, price)
VALUES (101, 'Wireless Mouse', 'Electronics', 29.99)
ON CONFLICT (category)    -- category is not unique!
DO UPDATE SET price = EXCLUDED.price;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Correct:

ON CONFLICT (product_id)   -- product_id is the primary key

Mistake 2: Updating the Conflict Column Itself

Updating the column that defines the conflict can cause unexpected results or errors:

Wrong:

INSERT INTO products (product_id, product_name, category, price)
VALUES (101, 'Wireless Mouse', 'Electronics', 29.99)
ON CONFLICT (product_id)
DO UPDATE SET
    product_id = EXCLUDED.product_id,    -- Updating the PK: dangerous
    price      = EXCLUDED.price;

In most cases this is either a no-op (the value is already the same) or causes a constraint violation if you try to change it to a different value. Avoid updating conflict columns.

Mistake 3: Confusing EXCLUDED with Table Values

In PostgreSQL, EXCLUDED refers to the incoming (attempted) values, while the table name refers to the current stored values. Mixing them up leads to logical errors.

-- Wrong: this sets price to the CURRENT value, not the new value (no-op)
ON CONFLICT (product_id)
DO UPDATE SET price = products.price;

-- Correct: this sets price to the INCOMING value
ON CONFLICT (product_id)
DO UPDATE SET price = EXCLUDED.price;

-- Useful: combine both (e.g., add incoming stock to current stock)
ON CONFLICT (product_id)
DO UPDATE SET stock_qty = products.stock_qty + EXCLUDED.stock_qty;

Mistake 4: MySQL Triggering on Unintended Unique Constraints

Since MySQL's ON DUPLICATE KEY UPDATE does not let you specify which constraint to match, a table with multiple unique indexes can produce surprises:

CREATE TABLE user_profiles (
    user_id   INT PRIMARY KEY,
    username  VARCHAR(50) UNIQUE,
    email     VARCHAR(100) UNIQUE,
    bio       TEXT
);

-- Which constraint does this conflict on? user_id? username? email?
-- MySQL will match ANY of them, potentially updating the wrong row.
INSERT INTO user_profiles (user_id, username, email, bio)
VALUES (1, 'new_username', 'existing@email.com', 'Hello')
ON DUPLICATE KEY UPDATE bio = VALUES(bio);

If user_id = 1 does not exist but existing@email.com belongs to user_id = 5, MySQL will update the row for user_id = 5. This is almost certainly not what you intended.

danger

When a table has multiple unique constraints in MySQL, ON DUPLICATE KEY UPDATE can match on any of them. This can lead to updating the wrong row entirely. In such cases, consider using a SELECT to check existence first, or migrate to a database that supports explicit conflict targets.

Mistake 5: MERGE Without Semicolon (SQL Server)

A surprisingly common error that produces a confusing error message:

-- Missing semicolon at the end
MERGE INTO products AS target
USING product_updates AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET target.price = source.price
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, price)
    VALUES (source.product_id, source.product_name, source.price)
-- ERROR!

Always terminate MERGE with a semicolon in SQL Server.

Performance Considerations

• Upsert is faster than check-then-insert. A single upsert statement eliminates the round trip of a preliminary SELECT and handles concurrency atomically.

• Index on the conflict column is essential. The database must quickly determine whether a conflicting row exists. Without an index on the conflict column (which primary keys and unique constraints provide automatically), performance degrades.

• Bulk upserts outperform row-by-row. Just like regular inserts, batching multiple rows into a single upsert statement reduces overhead significantly.

• MERGE can be expensive on large source tables. The MERGE statement performs a full join between source and target. If both tables are large, ensure the ON condition uses indexed columns.

• Watch for lock contention. Upserts acquire row-level locks. Under high concurrency, many processes upserting the same rows can create contention. Consider batching and retry logic.

• PostgreSQL ON CONFLICT and HOT updates. If your update only changes non-indexed columns, PostgreSQL can perform a Heap-Only Tuple (HOT) update, which is significantly faster. Avoid including indexed columns in your DO UPDATE SET clause unless necessary.

-- Use EXPLAIN to verify the upsert execution plan
EXPLAIN ANALYZE
INSERT INTO products (product_id, product_name, category, price)
VALUES (101, 'Wireless Mouse', 'Electronics', 29.99)
ON CONFLICT (product_id)
DO UPDATE SET price = EXCLUDED.price;

Quick Reference

Database	Syntax	Skip Duplicates
PostgreSQL	INSERT ... ON CONFLICT (col) DO UPDATE SET ...	ON CONFLICT DO NOTHING
MySQL	INSERT ... ON DUPLICATE KEY UPDATE ...	INSERT IGNORE
SQL Server	MERGE INTO ... USING ... WHEN MATCHED ... WHEN NOT MATCHED ...;	Omit WHEN NOT MATCHED
Oracle	MERGE INTO ... USING ... WHEN MATCHED ... WHEN NOT MATCHED ...	Omit WHEN NOT MATCHED
SQLite	INSERT ... ON CONFLICT (col) DO UPDATE SET ...	ON CONFLICT DO NOTHING

Summary

SQL UPSERT is the pattern of inserting a row if it does not exist or updating it if it does, all in a single atomic statement. Every major database supports this pattern, though the syntax differs.

Key takeaways:

• PostgreSQL uses INSERT ... ON CONFLICT (column) DO UPDATE SET, with EXCLUDED to reference incoming values. It offers the most precise control with explicit conflict targets and conditional WHERE clauses.
• MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, which triggers on any unique constraint violation. Use VALUES() (older syntax) or a row alias (MySQL 8.0.20+) to reference incoming values.
• SQL Server and Oracle use the MERGE statement, which provides the most flexible syntax with separate WHEN MATCHED and WHEN NOT MATCHED blocks, and SQL Server adds WHEN NOT MATCHED BY SOURCE for full synchronization.
• SQLite supports PostgreSQL-style ON CONFLICT since version 3.24. Avoid INSERT OR REPLACE as it deletes and re-inserts rather than performing a true update.
• Always use an upsert instead of the naive "check then insert/update" pattern. It is faster, simpler, and safe under concurrent access.
• Be careful with tables that have multiple unique constraints, especially in MySQL where the conflict target cannot be specified explicitly.
• For bulk operations, combine multi-row VALUES (PostgreSQL/MySQL) or staging tables (MERGE) with upsert syntax for maximum efficiency.

Upserts eliminate an entire category of application bugs related to race conditions and duplicate key errors. Learn the syntax for your database, and use it whenever your logic is "save this record, whether it is new or existing."