Why I Reach for UPSERT in My Daily ETL Work

When I’m loading nightly sales extracts into a reporting warehouse, the source system sometimes sends duplicate keys for the same day. In the past I would run a DELETE followed by an INSERT, or I would try to catch the unique‑violation error and retry. Both approaches add latency and risk leaving the table in an inconsistent state if something fails halfway through. Over the years I’ve settled on PostgreSQL’s INSERT … ON CONFLICT DO UPDATE (often called UPSERT) because it lets the database decide in a single, atomic statement whether a row should be created or refreshed.

The Real‑World Scenario

Imagine a table that stores daily aggregates for each store:

CREATE TABLE store_daily_sales (
    store_id      INT NOT NULL,
    sales_date    DATE NOT NULL,
    total_sales   NUMERIC(12,2) NOT NULL,
    total_units   INT NOT NULL,
    PRIMARY KEY (store_id, sales_date)
);

The ETL job receives a CSV with one row per store per day. Some rows represent corrections to previously loaded data, so the same (store_id, sales_date) pair may appear more than once in the file. The goal is to make sure the table ends up with the latest values from the file, without creating duplicate keys.

The UPSERT Pattern

Here’s the statement I use inside a psql script or a stored procedure:

INSERT INTO store_daily_sales (store_id, sales_date, total_sales, total_units)
VALUES
    ($1, $2, $3, $4)
ON CONFLICT (store_id, sales_date) DO UPDATE SET
    total_sales = EXCLUDED.total_sales,
    total_units = EXCLUDED.total_units;

The EXCLUDED alias refers to the row that would have been inserted. If a conflict on the primary key occurs, PostgreSQL updates the existing row with the values from EXCLUDED. Because the whole operation runs inside a single transaction, there’s no window where the table lacks a row for that key.

Why This Beats the Alternatives

  • Atomicity – No need for a separate DELETE; the DB guarantees that either the insert or the update happens.
  • Simplicity – One SQL statement replaces error‑handling loops in application code.
  • Performance – The planner can choose an index‑only path; there’s no extra scan for a DELETE.
  • Safety – If the UPDATE fails (e.g., a check constraint), the whole statement rolls back, leaving the original row untouched.

Handling More Complex Logic

Sometimes I need to accumulate values instead of overwriting them. For example, if the file contains incremental sales that should be added to the existing total:

INSERT INTO store_daily_sales (store_id, sales_date, total_sales, total_units)
VALUES
    ($1, $2, $3, $4)
ON CONFLICT (store_id, sales_date) DO UPDATE SET
    total_sales = store_daily_sales.total_sales + EXCLUDED.total_sales,
    total_units = store_daily_sales.total_units + EXCLUDED.total_units;

Here the store_daily_sales table appears on the right side of the assignment, letting us read the current values before adding the new ones. This pattern works for any commutative operation (sum, max, etc.) and keeps the logic inside the database where it’s fast and reliable.

Things to Watch Out For

Remember that ON CONFLICT only catches conflicts on indexes or constraints that you explicitly name (or the table’s PRIMARY KEY if you omit the conflict target). If you rely on a unique index that isn’t declared as a constraint, you must reference it by name: ON CONFLICT ON CONSTRAINT uniq_store_date.

Also, be aware that the EXCLUDED pseudo‑table cannot be used in a WHERE clause inside the DO UPDATE part; you must reference the target table directly if you need to filter rows.

Putting It All Together

In my ETL pipeline I bulk‑load the CSV into a temporary staging table, then run a single UPSERT that joins the staging data to the target:

INSERT INTO store_daily_sales (store_id, sales_date, total_sales, total_units)
SELECT s.store_id, s.sales_date, s.total_sales, s.total_units
FROM   staging_store_sales s
ON CONFLICT (store_id, sales_date) DO UPDATE SET
    total_sales = EXCLUDED.total_sales,
    total_units = EXCLUDED.total_units;

This approach scales nicely: the planner can use a hash join or merge join between the staging and target tables, and the conflict resolution happens row‑by‑row without extra round‑trips.

Final Thoughts

I’ve found that mastering the UPSERT pattern eliminates a whole class of “insert‑or‑update” bugs in my workflow. It makes the code shorter, the transactions safer, and the performance predictable. If you’re working with PostgreSQL (or any DB that supports a similar construct, like MySQL’s INSERT … ON DUPLICATE KEY UPDATE or SQL Server’s MERGE), give it a try in your next data load. You’ll likely wonder how you ever lived without it.