Right, so you’ve got data to insert. Maybe it’s a list of new users from a signup form, or a batch of sensor readings. You fire off a straightforward INSERT statement, and then… kaboom. A duplicate key violation. The whole operation fails, and you’re left holding the pieces. It’s like trying to seat a dozen people at a dinner table where one already-taken chair causes the entire party to be thrown out of the restaurant. It’s a ridiculous way to run a railroad.

This is where ON CONFLICT saunters in, puts on a pair of sunglasses, and says, “I got this.” The ON CONFLICT DO NOTHING clause is the specific incantation for when you want PostgreSQL to just shrug its shoulders and move on when it hits a duplicate. It’s the database equivalent of saying, “If a record with this key already exists? Cool, cool. Let’s just pretend I never tried to insert it in the first place and carry on with the rest.”

How It Works: The Syntax of the Casual Shrug

The magic happens by adding ON CONFLICT to your INSERT statement. You have to tell PostgreSQL which unique constraint or column is the one that might cause the conflict. You can target a specific constraint by name (the most robust way) or just a column name (which implies the unique index on that column).

Here’s the basic anatomy:

INSERT INTO your_table (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (target_column) DO NOTHING;

Let’s make this real. Imagine a table for user email addresses, where the email column has a unique constraint. Because letting two people have the same email is a bad idea.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL
);

Now, let’s try to insert a user who might already be there.

-- This will insert the user normally
INSERT INTO users (email, name)
VALUES ('ada.lovelace@example.com', 'Ada Lovelace')
ON CONFLICT (email) DO NOTHING;

-- This will DO NOTHING (and not error) because the email exists
INSERT INTO users (email, name)
VALUES ('ada.lovelace@example.com', 'Countess of Lovelace')
ON CONFLICT (email) DO NOTHING;

-- Let's prove it. You'll still only have one Ada.
SELECT * FROM users WHERE email = 'ada.lovelace@example.com';

The key thing to understand here is what “DO NOTHING” actually means. It doesn’t mean the statement failed. The statement succeeds. It just might have inserted zero rows. This is crucial. You need to check the output of your command, which will tell you the number of rows inserted (INSERT 0 1 for success, INSERT 0 0 for a conflict that was ignored).

Why You’d Use This Over Trying and Catching

You might be thinking, “Couldn’t I just query first to see if it exists, then insert?” Sure, you could. But don’t. That approach is prone to race conditions. In the microseconds between your SELECT and your INSERT, another process could swoop in and create the very duplicate you were trying to avoid. ON CONFLICT operates as a single, atomic command. It’s the bouncer at the door checking the list and letting people in all in one motion, eliminating the race condition entirely. It’s safer and almost always more efficient.

The Subtle Pitfall: Knowing What Actually Happened

The biggest “gotcha” with DO NOTHING is its silence. It doesn’t tell you which rows were skipped. If you’re inserting 100 rows in a single multi-insert statement and 5 of them conflict, the command will return INSERT 0 95. You know 95 went in, but you have no idea which 5 were duplicates. This is fine if you genuinely don’t care (“These are just log events, duplicates are meaningless”). It’s a problem if you need to know which ones failed so you can handle them elsewhere (“Which of these user signups were actually new?”).

For that, you’ll need the other, more powerful form of ON CONFLICT: DO UPDATE, which we’ll cover next. DO NOTHING is for when you’re truly, genuinely indifferent to the existence of the duplicate. It’s the ultimate “meh” of database operations.

Best Practice: Be Explicit with the Constraint

While ON CONFLICT (email) works, it’s better practice to explicitly name the unique constraint you’re targeting. This makes your code more robust. If someone drops the unique index on email and creates a new, different one later, your vague ON CONFLICT (email) might break or, worse, target the wrong constraint. Targeting by name is self-documenting and precise.

First, find the constraint name (it’s often tablename_columnname_key).

SELECT conname FROM pg_constraint
WHERE conrelid = 'users'::regclass AND contype = 'u';

Then use it:

INSERT INTO users (email, name)
VALUES ('grace.hopper@example.com', 'Grace Hopper')
ON CONFLICT ON CONSTRAINT users_email_key DO NOTHING;

So, use ON CONFLICT DO NOTHING when you want to bulletproof your inserts against duplicates you don’t care about. It’s a clean, atomic, and efficient way to say, “If it’s already there, just skip it.” It turns a potential error that stops your whole process into a minor non-event.