> if you are trying to report to the user "that code already exists, they must be unique" or "name missing, it must be provided" then you have failed input validation as that sort of thing should be picked up on much earlier in a request lifecycle IMO.

1. Not necessarily, most applications could use a well-designed schema to let the database do these validations and send improved results back to the user, it avoids duplicating (or triplicating) validation information. Especially for things like UNIQUE constraints, you're going to hit the database in-application when the DB will do it anyway? Unless it skips a significant amount of application work it's a complete waste of developer time (and likely application time as well).

2. A good schema can do significantly more complex, interesting and expensive (especially in-application if they need database data) validations than a mere NOT NULL check.

3. In-transaction issues can't be caught by the application server, they'll only blow up in the database.

> I'm surprised how often I still see a full exception report echoed all the way from the data layer of an application up to my browser.

Thus confirming that you're completely missing the point. Again, if one wanted to send database error messages to the end user one could already easily do so.

"[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").

You can't catch all errors in the application server. Data in transactions that haven't committed yet isn't available (usually) to the application servers.

> if you are trying to report to the user "that code already exists, they must be unique" or "name missing, it must be provided" then you have failed input validation as that sort of thing should be picked up on much earlier in a request lifecycle IMO.

"Name missing, it must be provided" is an internal validation of the entity which could, in principle, be done reliably in the application; "that code already exists, they must be unique" is a validation against other entities in the DB and therefore could not reliably be done anywhere except in the DB server itself if you have any concurrency.

I am trying to see how input validation addresses cases where the constraint is based on what else is in the database. It is conceptually much simpler and less error prone to let that class of issues be enforced only by the database rather than asking the db first "is this ok" and if so then doing another round trip to insert it (to be safe you'd have to lock the table).

Now, PostgreSQL can do any data validation you want to do in the application, it can do so at least partially declaratively. You can then pass back messages that the application can use in error handling.

I am actually working on frameworks which essentially delegate most of this to PostgreSQL. It's a very powerful approach but it means the db just doesn't trust the application.

> It's a very powerful approach but it means the db just doesn't trust the application.

Which has its advantages. The DB essentially becomes an API/service used by the application rather than an integral part thereof, and thus multiple applications can be cleanly plugged into the database instead of an "owner" application providing a service backed by the owned DB.

> The DB essentially becomes an API/service used by the application....

Exactly, which is what Martin Fowler is pushing with NoSQL... The idea that many people have been using RDBMS's as encapsulated databases for decades is usually lost on the NoSQL marketing though.

You can translate error into user friendly message easier when the source error is structured. You cannot as easily translate arbitrary error string to user friendly message.

>"name missing, it must be provided" then you have failed input validation as that sort of thing should be picked up on much earlier in a request lifecycle IMO.

Why? What is wrong with letting it get picked up where you already have the validation anyways instead of repeating the validation in two different languages?

What if there's two problems in the form? You generally just get one error from the database at a time.

That's really the only reason I can see to do it. But I can see plenty of cases where I don't care about that, so I don't think "you failed input validation" is at all accurate. The consequences are a more annoying UX, assuming it is something a user is interacting with. That doesn't sound like outright failure.