Testing against an in memory database means that you don’t need the full instantiation of PostgreSQL in order to run a test. Spinning up a full database takes time and resources. Each test needs to start from a clean slate.
An in memory database spins up instantly without needing to have a disk to persist the data to. It can be created and disposed of numerous times - once for each test. That way the database is always in a clean and known state when the tests starts - none of the other tests have impacted this test.
From msdn: https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/in-memory
The InMemory provider is useful when you want to test components using something that approximates connecting to the real database, without the overhead of actual database operations.
In most cases, the actual database connectivity is abstracted away. The developer isn’t writing sql for PostgreSQL or MySQL directly. The developer is working with an object relational mapping layer (ORM) that abstracts the raw database away. LINQ is one such that exists in C# ( https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/ ).
For these cases, it is important to trust the ORM will do the right thing when dealing with the database.
Another aside - often in memory databases are able to map their implementation to the quirks of a traditional database. An example of this is hsqldb and its ability to accept the grammar of different SQL databases - http://hsqldb.org/doc/2.0/guide/compatibility-chapt.html