Search code examples
c#sqldatabasepostgresqlparameters

Cannot change search_path persistently

I have a problem with PostgreSQL: when I use C# to connect to PostgreSQL, I always need to use SchemaName.Table. So I wonder how to fix it.

I found that they say use:

set search_path to SchemaName;  <---  It can work

Then, the first time I look my search_path with:

show search_path

I get:

 search_path
--------------
 mySchemaName

But in my C# code, I still need to add the schema name, so I look again, and I get:

 search_path
-----------------
 "$user", public
Solution

  • If I understood you right, you are wondering why changing search_path with SET isn't persistent.

    SET only changes a parameter for the current database session. If you want to change search_path for a certain database in a persistent fashion, use

    ALTER DATABASE mydbname SET search_path = myschemaname;

    The new setting will not become effective for current sessions, only sessions established after you run the above statement.