Hello, If I set search_path to one of the schemas, I can use psql without writing the qualified names. In my application instead the server returns error if I dont use select * from myschema.mytable. Is this fixable?
We can achieve in two ways for that:
a. Make "Jforum'' << new schema name>> the default schema for a session
b. Make "Jforum" <<new schema name>> the default schema permanently
a) That can be done by session level using the following steps:
First, check the search_path variable by running:
show search_path;
sample output: "$user", public, sys, dbo
Then add the schema by running:
SET search_path to "$user", jforum, public, sys, dbo (Where jforum is the new schema)
Now if you create any new tables they will be part of the 'jforum' schema for a specific session in “psql” until you do a \q and quit out from the “psql”. You have to do it again if you login again from “psql” as it is for one session only.
b) That can be done permanentlyusing the following steps
1. Stop your database server
2. Open the postgresql.conf file in your 'data' folder and then search for 'search_path' variable. By default the value for 'search_path variable be like
search_path = '$user,pulic,sys,dbo'
Please change it in the following way for your schema Jforum:
search_path = '$user,jforum,pulic,sys,dbo'
3. Now after the change is done, save and close the postgresql.conf file.
4. Start the database server.
Now when you do:
create table test1(a number);
That will be made automatically in the 'jforum' schema.
