This can be a good fast/local test or maybe a sanity test ... but there are definitely differences between databases that need to be accounted for. You wanna take that green test pass with a bit of skepticism. So you always want to test on the same DB engine that is running your prod workloads. If your surface area is small, you can get by with the approach you mentioned, but it would need to be marked tech debt that should be paid down as soon as possible, or as soon as that bug that manifests itself in PSQL but not sqlite appears :)
(Update: I just looked in our test code, you can also replace the CREATE TABLE commands with "CREATE UNLOGGED TABLE" to disable write-ahead logging.)
There are possibly other tricks?
I slightly disagree with the tech debt comment, though. If you get a huge speed up, it may be worth paying for the occasional bug, depending on the circumstances. Or you could do both, and only run the test on Postgres occasionally.
Sqlite is the most popular database in the world by a large margin. While you are correct for those who use other databases, the majority case you are wrong.
Of course most people who have complex queries are probably not using sqlite and so may not care about testing the database.
