Posts

Showing posts from December, 2011

Postgresql PostGIS How to set your search_path

The problem: You want to have all your spatial data in a schema other than "public" in your Postgresql PostGIS database and don't want to have to always specify the schema.tablename when creating new tables. The solution: Close the connection to the database you want to change run the sql query                 ALTER DATABASE DatabaseName SET search_path = '$user','geodata', 'public';              in this example the schema 'geodata' is the primary schema where I want to store all my spatial tables.  The order in which Postgresql "searches" where to create the table is then defined in the same order as you have listed in your SET search_path statement. If you just want to change the search_path for a current session you can use: SET search_path = '$user','geodata', 'public'; now you can merrily go and create a new table and it will automatically be created in your 'geodata' schema.