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:

1. Close the connection to the database you want to change
2. 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.

if you already have the tables in the 'public' schema and want to move them to your new shiny 'geodata' schema it is really easy to do:

ALTER TABLE name SET SCHEMA new_schema

now if you do this don't forge that your geometry_columns won't automatically reflect your schema changes and must be updated by using

SELECT Populate_Geometry_Columns('geodata.myspatial_table'::regclass);

cheers
Michael