Avoid Relying On Error Codes To Optimistically Insert In Postgres

Understanding the difference between INSERT and INSERT..ON CONFLICT

Okay, I admit I was considering this. My Go-like treatment of errors as “just another value” had me wondering if I could avoid writing an INSERT with an ON CONFLICT DO UPDATE clause, and instead do this in code:

// This is bad
 
err := db.doInsert(data)
if errors.Is(err, sql.ErrConflict) {
  db.doUpdate(data)
}

But it felt wrong, and now I know that it would’ve been inefficient too.

I did the right thing in the end, and used an INSERT..ON CONFLICT DO UPDATE statement. A bit of extra effort at the time, but it’s was the better thing to do.