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

Comments

  1. Defying Pyramid PostgreSQL Connection Issue? Contact to PostgreSQL Remote Database Service to settle it
    With the help of Postgres SQL Support for Linux or Postgres SQL Support for Windows you can without a lot of an extend screen the execution of your Postgres database and perceive if there is any issue. Well in case you are dumbfounding about your affiliation issue by then quickly make our medicinal move and resolve main problems even before it hits your end customers. Our PostgreSQL Relational Database Service can without a doubt track the execution of your entire Postgres condition.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete

Post a Comment

Popular Posts