The problem is your mock need to implement the same isolation level as your real DB and support transaction ...
You could use SQLITE in memory DB to run your test but that would make your test a lot slower I assume.
At that point—why bother with the mock at all? Cutting out the intermediary and running integration tests of your application against a real database will be faster.
You can’t use SQLite here either because all SQLite isolation levels are serializable anyway, which is _very_ different from how PostgreSQL works. Testing against SQLite could end up giving you a false sense of security that your code is safe against race conditions, whereas in reality it’s vulnerable when connected to a PostgreSQL server because of the difference in default isolation levels
At this level of testing detail the only real option is to test against a real database that matches what you’re running in production. Otherwise you’re just testing a mock.
Except in the case of shared cache database connections with PRAGMA read_uncommitted turned on, all transactions in SQLite show "serializable" isolation