'In .NET 5 why am I unable to access PostgreSQL tables in a custom schema?

We have a custom schema (config) in our DB where we have multiple tables declared and populated. In DataGrip and pgAdmin alike we're able to see the tables in the custom schema. The following query shows us tables in both the public schema as well as in the custom schema:

select table_schema, table_name 
from information_schema.tables
where not table_schema='pg_catalog' 
  and not table_schema='information_schema';

In DataGrip we had to "show all schemas"; in pgAdmin, everything was visible right away, tables from both schemas.

However, in .NET code, when we run the same query, we only see the public tables.

await using var conn = new NpgsqlConnection(connString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(
    @"select table_schema, table_name 
            from information_schema.tables
            where not table_schema = 'pg_catalog' 
              and not table_schema = 'information_schema';", conn);

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
    Console.WriteLine(reader.GetString(0) + "." + reader.GetString(1));

The connection string does include: ;SearchPath=config,public

All we're getting back from that code is tables in public.

postgresqlnpgsql


Solution 1:[1]

We noticed that there is a hostname for the cluster and a hostname for the DB instance. We were using one (I think it was the instance) and tried using the other (cluster hostname) and suddenly the .NET code was able to see the config schema tables.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jon Davis

Related Questions