[postgis-users] Install postgis on a different schema

[Kevin Neufeld]

P Kishor wrote:
> In the above note, should I be setting the search_path to the
> different project schemas as well, and not just 'public' and 'shared'?

Correct.


> So, if I have the following schemas in mydb, 'public', 'shared',
> 'project1', 'project2' then the above command really should be
> 
> ALTER DATABASE mydb SET search_path = public, shared, project1, project2;


That works, but I would recommend you reverse the order.  Whenever you query the database, postgres looks for your 
referenced table/view/function in the schema order listed.  So if you issued
SELECT * FROM mytable;
in this case Postgres would look first in public, then in shared, then in project1 and then project2 for "mytable". 
This implies that you can have multiple tables with the same name living in different schemas (which has it's own 
advantages from a modeling perspective).  Anyway, the point is that if you are working on project2, you wouldn't want to 
accidentally reference a table with the same name in project1.

Also, I would recommend against putting multiple projects in the search_path at the same time for the above reason. Note 
that search_paths are user dependent as well.  So if you are actively working on project2 you can set the search_path 
just for yourself.
ALTER USER myusername SET search_path TO project2, shared, public;
(taking effect the next time you start a session)

You can also change the search_path for the current session on-the-fly by:
SET search_path TO project1, shared, public;


> 
> And, if I add another schema, 'project3', a few months later, then I run
> 
> ALTER DATABASE mydb SET search_path = public, shared, project1,
> project2, project3;

Again, reverse the order.  My personal preference is not to alter the global search_path for all users who log in, so I 
would never do this.  Personally, I would set the global path simply to "shared, public" and leave the individual 
project references to each user.  There is also a limit to the number of references you can put in the search_path, so 
after you add the 8th or 9th project, this statement would error out.


> 
> Correct?
> 
> By the way, what is that search_path doing? What is it for? 

See above.


And, why
> don't I have to set it if I have only a 'public' schema? Is that
> because by default, a public schema doesn't need an explicit search
> path?

The default search_path for all users is '"$user", public'.  So this means that if you had a personal schema with the 
same name as your username, all new objects would be placed in your personal schema and all table references would start 
there.

If you left off public from the search_path, you'd no longer be able to see the PostGIS installation.

Hope this helps,
Kevin