Search code examples
c#postgresqlnpgsqlsearch-path

Access a postgres table using the schema prefix in c#

I'm using postgres in a c# project and i'm trying to make some basic queries such as

qry.CommandText = "select * from LOGIN";
NpgsqlDataReader qryReader = qry.ExecuteReader();

But it says that the table LOGIN does not exist.

I already know that the following query works: qry.CommandText = "select * from \"myDataBase\".LOGIN"; but I would prefer not using it.

I also know from this thread that I can use SET search_path TO myschema,public; to access the table without prefix in psql command line, but I don't see how it would work for my c# projet.

Also, I have another project where I don't need the prefix but I don't know why it works for my other projet and not this one.

Any help would be very appreciated.

Thanks

Solution

  • While SET search_path TO myschema,public; sets the search_path for the current session only, you can make it permanent for the user running the command:

    ALTER USER username SET search_path TO myschema, public;