For many small Rails web applications, details having sane (and rich) datetime support in the database is both nearly invisible (turning bugs into errors, and working about the same otherwise) and very useful. The same could be said about silent truncations of strings (one can get the same behavior in Postgres by using a explicit cast, or just a built-in function).

LedgerSMB -- an author of which wrote this article -- is on the advanced side of the spectrum in using specific Postgres-isms, and that's not for everyone.

There are grades in-between: using arrays for simple kinds of matching in Postgres is not rocket science and can make your life a lot easier (and faster):

  SELECT * FROM blawg WHERE tags && '{a,b,c}';

Yet another use case might be full-text search, where integrating and synchronizing an external information retrieval system (like Lucene, Solr) for simple search use cases are just more trouble than it is worth. With slightly more work than the above, one can use Postgres's Full Text Search feature.

So here I think you are supposing a false dichotomy: there is a continuum available on both databases, but the continuum extends a lot more into 'the database can know something about the data' territory in the Postgres implementation.