"[if you get a unique constraint violation] ... then you have failed input validation"
Due to concurrency, it's almost impossible to enforce unique constraints properly without actually executing the insert/update. Sometimes you can catch it ahead of time, but it's not guaranteed.
Knowing precisely which constraint failed helps improve the error message you give to the user, which may allow them to correct the problem. For instance, if you have two unique constraints involved in a transaction, your application can figure out which one was violated, and you can use that to give the user directions to correct it (e.g. "choose a different username" versus "that email address already has an account here, click here to send you the username").
Due to concurrency, it's almost impossible to enforce unique constraints properly without actually executing the insert/update. Sometimes you can catch it ahead of time, but it's not guaranteed.
Knowing precisely which constraint failed helps improve the error message you give to the user, which may allow them to correct the problem. For instance, if you have two unique constraints involved in a transaction, your application can figure out which one was violated, and you can use that to give the user directions to correct it (e.g. "choose a different username" versus "that email address already has an account here, click here to send you the username").