SQLite is indeed a very good piece of software. It's also a fairly idiosynchratic piece of software: For example, table schemas are essentially untyped, and you can assign any type of value to a column value. The lack of good support for date/time data is problematic. And the complete lack of support for GIS is pretty serious; for many people, PostGIS is the "killer app" for Postgres.
I agree with your general sentiment, but there actually exists a GIS extensions for SQLite, SpatiaLite[1], so there is no "complete lack of support for GIS". Still I think PostGIS is vastly superior to most if not all of its competitors.
