I'm reading Oreilly's PostgreSQL: Up and Running, and in the section Organizing Your Database Using Schemas it says the search_path
may be set at the database level:
For example, if we wanted all objects in contrib to be accessible without schema qualification, we would change our database as follows:
ALTER DATABASE mydb SET search_path="$user",public,contrib;
However, after typing the command above, I still can't refer to tables in schemas other then public
without qualifying them:
ALTER DATABASE auth SET search_path="$user",public,staging;`
\d users
Gives:
Did not find any relation named "users".
But if I use \d staging.users
, it works.
SHOW search_path;
Gives:
search_path
----------------
"$user",public
Does the default search_path
from postgresql.conf
override the per-database value set with ALTER DATABASE ... SET search_path
? If that's the case, what's the use of the per-database value?
Best Answer
The citation from the
SET
related paragraph of theALTER DATABASE
documentation section is "Whenever a new session is subsequently started in that database, the specified value becomes the session default value". So the changes will take effect for new sessions only. You just need to reconnect.