Wednesday, November 9, 2011

Postgresql - Persistent default search path (schema) in a per user basis

I'm entitled with the task of giving a sql course to my coworkers and I want to spare them the details of dealing with schemas or "search paths" in the postgresql lingo, as the tables we'll be working with are not in the public schema, and I don't want to go into the  details of what a schema is, just yet, as many of them don't have any previous experience with databases.

So I created them a user, and to change it's default search path you only need to fire this up:
ALTER USER username SET search_path = schema1,schema2,schema3,etc;

But have in mind that if you plan to work with this user in another databases where the set schema doesn't exist, you won't be able to see any table unless you set the correct search_path. So I guess, you better always add the public schema to the default search path for the user.

No comments:

Post a Comment