I think you need a unique index if you're doing something like this in MS-SQL, Postgres, or MySQL. Otherwise, the default isolation level won't prevent this data race:

  writer A: begin xact, update, see 0 rows updated
  writer B: begin xact, insert same thing, end xact
  writer A: insert, end xact

One reason I use ON CONFLICT in Postgres is for explicitness. You're specifically telling the DBMS to upsert, and it'll throw an error if you don't have a unique index to support this.

Also mentioned above, ON CONFLICT DO UPDATE would be able to update the concurrent writer's row instead of throwing a uniqueness violation.