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 S_chema to any other Schema and update the PostGIS metatables (and leave these in the _public _Schema). See the function postgis_schema_move() below.

 1-- Function to move tables to schema
 2CREATE OR REPLACE FUNCTION
 3       public.postgis_schema_move(old_schema varchar(32),
 4         new_schema varchar(32)) RETURNS void AS $$
 5DECLARE
 6    row record;
 7BEGIN
 8    FOR row IN SELECT tablename FROM pg_tables
 9            WHERE schemaname = old_schema and tablename != 'spatial_ref_sys'
10              AND tablename != 'geometry_columns'
11    LOOP
12        EXECUTE 'ALTER TABLE ' || quote_ident(old_schema) || '.'
13                 || quote_ident(row.tablename) ||
14                    ' SET SCHEMA ' || new_schema || ';';
15        EXECUTE 'UPDATE public.geometry_columns
16                 SET f_table_schema = ' || quote_literal(new_schema) ||
17                  ' WHERE f_table_schema = ' || quote_literal(old_schema) ||'
18                    AND f_table_name = ' || quote_literal(row.tablename) || ';';
19    END LOOP;
20END;
21$$ LANGUAGE plpgsql;
22
23-- Example: Move from public to schema app
24SELECT public.postgis_schema_move('public', 'app');</pre>

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.