Right, so you’ve got data. You want to put it in a table. But here’s the rub: some of it might already be there. The classic, tedious way to handle this is a dance: SELECT to check, then either INSERT or UPDATE. It’s clunky, it’s prone to race conditions, and frankly, it’s beneath you. Enter ON CONFLICT DO UPDATE, PostgreSQL’s glorious gift to humanity, often called an “UPSERT.” It’s a single, atomic operation that says, “Listen, if this insert would violate a unique constraint, just do this update instead.”

The magic here is all about unique constraints. This isn’t some vague matching on any column; it’s specifically for when your shiny new row trips over a UNIQUE index or PRIMARY KEY. You have to tell PostgreSQL which unique violation you’re planning to handle.

The Anatomy of a UPSERT

The syntax looks like this. Pay attention to the conflict_target; it’s the star of the show.

INSERT INTO table_name (column1, column2, ...)
VALUES (...)
ON CONFLICT (conflict_target) -- This is the unique column(s)
DO UPDATE SET
  column1 = EXCLUDED.column1,
  column2 = EXCLUDED.column2;

The special EXCLUDED table is your best friend here. It’s not a real table; it’s a magical alias that represents the row you tried to insert. This is how you access the new values you wanted to put in. Without it, you’d have to manually retype all the VALUES(...) stuff, which is a recipe for errors and madness.

Let’s make this concrete. Imagine a table for user profiles.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL, -- This is our conflict target
    name TEXT NOT NULL,
    login_count INTEGER DEFAULT 0
);

Now, let’s say a user tries to sign up, but we also want to update their name if they already exist and bump their login count. Here’s how we do it:

INSERT INTO users (email, name)
VALUES ('ada@lovelace.com', 'Ada Lovelace')
ON CONFLICT (email) -- Conflict on the unique 'email' column
DO UPDATE SET
    name = EXCLUDED.name, -- Use the new value from the insert attempt
    login_count = users.login_count + 1; -- But reference the OLD value from the table

See what happened there? For name, we want to overwrite the old value with the new one, so we use EXCLUDED.name. For login_count, we want to increment the existing value, so we reference users.login_count. This is the crucial distinction. If you wrote login_count = EXCLUDED.login_count, you’d set it to NULL or DEFAULT because your INSERT didn’t provide a value for it. You’d have just reset everyone’s login count to zero. Don’t do that. It’s a classic blunder.

Beyond the Basics: The WHERE Clause and Partial Indexes

Sometimes, you don’t want to update on every conflict. Maybe you only want to overwrite values if the new data is more recent. You can add a WHERE clause to the DO UPDATE part.

INSERT INTO user_profiles (user_id, bio, last_updated)
VALUES (123, 'New and improved bio!', NOW())
ON CONFLICT (user_id)
DO UPDATE SET
    bio = EXCLUDED.bio,
    last_updated = EXCLUDED.last_updated
WHERE EXCLUDED.last_updated > user_profiles.last_updated; -- Only update if newer

This is incredibly powerful. But here’s a pro-tip: you can pair this with a partial unique index for even more surgical precision. Let’s say you only care about uniqueness for active users.

CREATE UNIQUE INDEX unique_active_email ON users (email) WHERE is_active;

Your ON CONFLICT clause can then target that specific index, preventing conflicts only for active users and ignoring duplicates in the inactive pile.

INSERT INTO users (email, name, is_active)
VALUES ('old@user.com', 'Old User', true)
ON CONFLICT ON CONSTRAINT unique_active_email -- Target the specific index by name
DO UPDATE SET ...;

The Pitfalls and The Power

First, the big one: You must specify a conflict target. Just writing ON CONFLICT DO NOTHING works without a target, but ON CONFLICT DO UPDATE requires it. PostgreSQL isn’t psychic; it needs to know which unique constraint you expect to be violated.

Second, remember that ON CONFLICT is triggered by unique constraints and nothing else. It won’t catch CHECK constraint violations or foreign key violations. Those are still errors and will cause the entire statement to fail.

Finally, use it wisely. It’s a sharp tool. The atomicity is its greatest strength—it eliminates the race condition between the SELECT and the subsequent INSERT/UPDATE that has plagued developers for decades. You’re telling the database your entire intent in one go, and it can handle it without any other transactions getting in the way. It’s not just convenient; it’s correct.