Moving PostGIS tables from the public schema to a new schema

Update 30 nov 2013: code is now as a GIST on GitHub, also with function to move single table.

Update 19 feb 2014: not yet tested with PostGIS v2.0 and up, so beware (or let me know if that works)!

Update 4 june 2015: tested with PostgreSQL 9.3.4 and PostGIS v2.1: all ok!! 

Using PostgreSQL Schema’s when using PostGIS is very useful. Instead of the default public schema where PostGIS and its meta-tables (geometry_columns and spatial_ref_sys) are installed one can use an explicit schema. One main reason, at least for me, is that PostgreSQL Schema’s allow me to make data dumps of the Schema (via pg_dump)  and restore these dumps in another database, even on another system. When using the default public schema a dump would also include PostGIS functions and metatables. This is problematic to restore on another system or higher PostgreSQL/PostGIS version. So I recommend always to use Schema’s.

But what if your tables are already in the public schema? This was the case in some of my older projects like GeoSkating which has tables in PostgreSQL 8.2 in the public schema. With some hacking and surfing on the web, I constructed an SQL function that would move my tables from the public Schema to any other Schema and update the PostGIS metatables (and leave these in the public Schema). See the function postgis_schema_move() below.

-- Function to move tables to schema
CREATE OR REPLACE FUNCTION 
       public.postgis_schema_move(old_schema varchar(32), 
         new_schema varchar(32)) RETURNS void AS $$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT tablename FROM pg_tables 
            WHERE schemaname = old_schema and tablename != 'spatial_ref_sys' 
              AND tablename != 'geometry_columns'
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(old_schema) || '.' 
                 || quote_ident(row.tablename) || 
                    ' SET SCHEMA ' || new_schema || ';';
        EXECUTE 'UPDATE public.geometry_columns 
                 SET f_table_schema = ' || quote_literal(new_schema) || 
                  ' WHERE f_table_schema = ' || quote_literal(old_schema) ||' 
                    AND f_table_name = ' || quote_literal(row.tablename) || ';';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Example: Move from public to schema app
SELECT public.postgis_schema_move('public', 'app');

The SQL function postgis_schema_move() takes two string arguments: the old_schema and new_schema, the old (usually ‘public’) and  new schema names. The new schema needs to be created first. Also moving back from a new schema to the public schema works.

3 Comments:

  1. Pingback: Conocimiento Abierto » Blog Archive » Comentario sobre el diseño de bases de datos PostGIS

  2. koech Nicholas

    Thanks you so much! The function saved me a lot of trouble 😉

  3. Pingback: New website – but what is Open Geospatial? –   Just Objects

Leave a Reply

Your email address will not be published.