Consider how we would traditionally develop software using a conventional Test-Last approach. We read our requirement spec (whatever form that takes), perform some design work -- as much as we feel we need to go forward with some confidence, and we start cutting code. Time goes by. Our codebase grows. We're doing some informal testing as we go along to feel confident that the code under development does what we think it should be doing...
And here's Important Point Number One: "the code does what we (the developers!) think it should be doing". Not what the business thinks it ought to be doing. See? We're inherently unsure -- even if only subconsciously -- whether we're doing work that actually serves the business. And unsureness is insidious and destructive to our well-being. We feel much better about ourselves as developers when we know that we're producing useful stuff that really has value. So here's the first way that having tests defined up-front helps us preserve our mental balance: With a reasonable set of tests
But there is, I think, a deeper psychological value to the Test-First approach.
So much of the time -- I will thumbsuck something like 99% of the time -- our code is broken. The system we're working on doesn't work. This is inherent in the fact that it takes us time to design and write software, and the continual not-workingness can be powerfully demoralising. Especially when we (finally, right towards the end of the project lifecycle) start formal testing, and our tester (often users) start coming back with bug after bug after bug after bug, and all that beautiful code we're so proud of is labelled "Less Than Satisfactory" -- usually in much stronger language than that.
Herein lies the secret destructiveness of Test-Last. Our code is always broken, and we can never do more than run after the brokenness trying to patch it up, until, at last, demoralised and exhausted, we move on to the next job or project.
Contrast this with a Test-First pattern.
We first write a bunch of tests. Sure it takes some time, can often be pretty tedious, and is frequently little more than a first-pass best-guess at what the code is supposed to do. Almost certainly we will add more tests as our code grows and evolves and begins to tell us the shape it wants and needs to be. But at least we start with a battery of (automated!) tests. And we start with the expectation of them all failing. This is by definition! We haven't written the necessary code, yet. A short time later we may have enough of the boilerplate in place that we can at least compile everything and actually run our tests, but we still expect them all to fail because our methods all read
throw new UnsupportedOperationExcetion( "TODO" );
So we run our tests, and they all fail. And we're happy about that! Everything went as we predicted. We feel in control.
As we go along replacing those stubbed-out methods, some of our tests begin to go green. We're making visible progress towards a well-defined end goal. We gain an ever-increasing feeling of confidence and accomplishment. We have tangible, objective evidence that we're making progress. Even when we make some of the "test bar" revert somewhat to red -- because we add new tests, or because we do some large-scale refactoring that breaks stuff -- we still feel good about it, because we know that our self-built safety-net is working! We've actually decreased the likelihood of things going wrong, so we know we're enhancing the quality of our product. (And every good developer I've ever worked with has an inner Proud Craftsman lurking in their soul.)
So it seems to me that Test-First builds our confidence and self-esteem as we go along, whereas Test-Last contains an inherently destructive, soul-sapping subversiveness to it.
I think this value outweighs the sum of all the other benefits (many though they are) of Test-First.
 Note that I make no distinction, here, between Unit Tests, Functional Tests, or System Tests (which would probably involve real databases, external systems, and so forth, albeit serving test data to our system-under-test.) I don't really care what form of Test-First is most useful to your team and situation. It's the principle that counts!