Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


Did not find any relation named "users".

But if I use \d staging.users, it works.

SHOW search_path;



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?

share|improve this question
can you select * from users? – Jack Douglas Aug 10 '12 at 18:32
No, I can't select * from users but I can select * from staging.users. – Daniel Serodio Aug 10 '12 at 18:41
Did you reconnect to your database after setting a new default for its search_path? Did you try just setting the search_path for your connection? – kgrittn Aug 10 '12 at 18:46
I hadn't reconnected yet. Reconnecting psql solved my problem, thanks. Would you write an answer so I can mark it as accepted? – Daniel Serodio Aug 10 '12 at 19:39
up vote 3 down vote accepted

The citation from the SET related paragraph of the ALTER 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.

share|improve this answer
Without reconnecting, to modify search_path on the current session (in your psql session, for example) you can use SET search_path TO "$user",public,contrib;. – Matt May 14 at 14:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.