I do what you suggest: run dev/test against a real PostgreSQL (of the same version) running in a container. In fact, I take this further and run builds against an actual PostgreSQL, since I like to generate code based on the DB schema using jOOQ [3].
The build proceeds like so:
1. Spin up a fresh PostgreSQL container
2. Run my schema migrations against it
3. Run the jOOQ code generator against it
4. Compile code
This provides some additional guarantees, including:
1. My queries are fully type-safe from my code down to the database schema level
2. My schema migrations can be run against an empty database
[1] https://www.h2database.com/html/main.html
This at least is possible to fake. But in the era of premade docker packages for postgres, I do wonder why the practice you describe persists.
The Micro-Orm I use in C# manages to work around this well enough; the only thing SQLite is really hard to work with is Time since there's no real structure defined for it.