Often times when you start a new database, you put all your tables and views in the public schema. For databases with few tables and all relatively commonly grouped data, the benefits of using schemas for logical groupings vs. the downside of having to reference it with the schema name is more trouble than its worth.
As time goes by and with organic growth, this simple database you had that does one thing suddenly starts doing a lot of other things you hadn't initially planned for. Now to keep your sanity you really need the benefit of schemas for logical groupings. How do you retroactively do this? The answer is not quite as easy as one would hope. Ideally you would want to do a RENAME from public.sometable to newschema.sometable, but that doesn't work.
Answer:UPDATE:
As Tom Lane and David Fetter have noted - 8.1 and above introduced a
ALTER TABLE name SET SCHEMA new_schema command, which is documented in
8.1-8.3 ALTER TABLE docs
so the below code is unnecessary for PostgreSQL 8.1 and above.
Fiddling directly with the raw PG Catalog is generally a bad thing to do and its very likely we forgot some steps below. Apologies if we encouraged any bad habits.
During our search, we did find a pg function in the newsgroups, submitted by Chris Traverse that moves tables from one schema to another and that more or less seems to do it. As many have noted in the comments of this article, this function is missing lots of parts. Below is our attempt to fill in these missing parts for pre-PostgreSQL 8.1 installs. PostgreSQL 8.1 should use the ALTER TABLE SET SCHEMA approach instead. We also needed a function that would also correct geometry_columns meta table for postgis spatial database and ALTER TABLE does not do that. Below are our 2 revised functions based on the above. NOTE we had to change it from LANGUAGE sql to LANGUAGE pgsql because we needed to verify geometry_columns table existed before trying to update it. In doing so since pgsql supports alias names and sql doesn't we were able to discard the $1, $2 etc and have more meaningful names for variables. We were also able to simplify the SQL update statements by declaring the old and new schema ids once.
-- Pre 8.1 version - USE at your own risk since we didn't have an old install to test this on
CREATE OR REPLACE FUNCTION cpmove_relation(param_tblname character varying,
param_source_schema character varying,
param_dest_schema character varying)
RETURNS boolean AS
'
-- param_tblname is the table name
-- param_source_schema is the source schema
-- param_dest_schema is the destination schema
DECLARE
new_schema_oid oid;
old_schema_oid oid;
tblname_oid oid;
BEGIN
new_schema_oid := (SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname = param_dest_schema);
old_schema_oid := (SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname = param_source_schema);
tblname_oid := (SELECT oid FROM pg_catalog.pg_class
WHERE relname = param_tblname AND relnamespace = old_schema_oid);
IF new_schema_oid IS NULL or old_schema_oid IS NULL THEN
RAISE NOTICE ''schema or table is invalid'';
RETURN false;
ELSE
/**Correct table location **/
UPDATE pg_catalog.pg_class
SET relnamespace = new_schema_oid
WHERE relnamespace = old_schema_oid
AND relname = param_tblname;
UPDATE pg_catalog.pg_type
SET typnamespace = new_schema_oid
WHERE typnamespace = old_schema_oid
AND typname = param_tblname;
/**update hidden table type **/
UPDATE pg_catalog.pg_type
SET typnamespace = new_schema_oid
WHERE typnamespace = old_schema_oid
AND typname = ''_'' || param_tblname;
/**Correct schema dependency **/
UPDATE pg_catalog.pg_depend
SET refobjid = new_schema_oid WHERE refobjid = old_schema_oid AND
objid = tblname_oid AND deptype = ''n'';
/**Correct schema of all dependent constraints that were in old schema to new schema **/
UPDATE pg_catalog.pg_constraint
SET connamespace = new_schema_oid
WHERE connamespace = old_schema_oid
AND oid IN(SELECT objid FROM pg_depend WHERE refobjid = tblname_oid);
/**Correct schema of type object of dependent constraint index that were in old schema **/
UPDATE pg_catalog.pg_class
SET relnamespace = new_schema_oid
WHERE relnamespace = old_schema_oid
AND oid IN(SELECT d.objid
FROM pg_catalog.pg_depend As p INNER JOIN pg_catalog.pg_depend As d ON p.objid = d.refobjid
WHERE p.refobjid = tblname_oid AND d.deptype = ''i'');
/**Correct schema of type object of dependent index that were in old schema **/
UPDATE pg_catalog.pg_class
SET relnamespace = new_schema_oid
WHERE relnamespace = old_schema_oid
AND oid IN(SELECT d.objid
FROM pg_depend As d
WHERE d.refobjid = tblname_oid AND d.deptype = ''a'');
/**Correct schema of class object of related sequence object that was in old schema **/
UPDATE pg_catalog.pg_class
SET relnamespace = new_schema_oid
WHERE relnamespace = old_schema_oid
AND relname
IN(SELECT param_tblname || ''_'' || c.column_name || ''_seq''
FROM information_schema.columns c
WHERE c.table_name = param_tblname AND c.table_schema = param_source_schema);
/**Correct schema of type object of related sequence object that was in old schema **/
UPDATE pg_catalog.pg_type
SET typnamespace = new_schema_oid
WHERE typnamespace = old_schema_oid
AND typname
IN(SELECT param_tblname || ''_'' || c.column_name || ''_seq''
FROM information_schema.columns c
WHERE c.table_name = param_tblname AND c.table_schema = param_source_schema);
/**Update schema dependency of related class object of sequence object **/
UPDATE pg_catalog.pg_depend
SET refobjid = new_schema_oid
WHERE refobjid = old_schema_oid AND
objid IN(SELECT c.oid
FROM pg_catalog.pg_class c
INNER JOIN information_schema.columns co
ON (c.relname = param_tblname || ''_'' || co.column_name || ''_seq'')
WHERE c.relnamespace = new_schema_oid AND co.table_name = param_tblname)
AND deptype = ''n'';
/**Correct postgis geometry columns **/
IF EXISTS(SELECT table_name
FROM information_schema.tables
WHERE table_name = ''geometry_columns'' AND table_schema = ''public'') THEN
UPDATE public.geometry_columns SET f_table_schema = param_dest_schema
WHERE f_table_schema = param_source_schema and f_table_name = param_tblname ;
END IF;
RETURN TRUE;
END IF;
END
'
LANGUAGE 'plpgsql' VOLATILE;
--Post 8.0 installs
CREATE OR REPLACE FUNCTION cpmove_relation(param_tblname character varying,
param_source_schema character varying,
param_dest_schema character varying)
RETURNS boolean AS
$$
-- param_tblname is the table name
-- param_source_schema is the source schema
-- param_dest_schema is the destination schema
DECLARE
new_schema_oid oid;
old_schema_oid oid;
tblname_oid oid;
BEGIN
new_schema_oid := (SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname = param_dest_schema);
old_schema_oid := (SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname = param_source_schema);
tblname_oid := (SELECT oid FROM pg_catalog.pg_class
WHERE relname = param_tblname AND relnamespace = old_schema_oid);
IF new_schema_oid IS NULL or old_schema_oid IS NULL or tblname_oid IS NULL THEN
RAISE NOTICE 'schema or table is invalid';
RETURN false;
ELSE
EXECUTE('ALTER TABLE ' || param_source_schema || '.' || param_tblname || ' SET SCHEMA ' || param_dest_schema);
/**Correct postgis geometry columns **/
IF EXISTS(SELECT table_name
FROM information_schema.tables
WHERE table_name = 'geometry_columns' AND table_schema = 'public') THEN
UPDATE public.geometry_columns SET f_table_schema = param_dest_schema
WHERE f_table_schema = param_source_schema and f_table_name = param_tblname ;
END IF;
RETURN TRUE;
END IF;
END
$$
LANGUAGE 'plpgsql' VOLATILE;
To use do SELECT cpmove_relation('mytable', 'public', 'mynewschema');
If you want to move multiple tables at once to a new schema - you can do something like this
SELECT cpmove_relation(table_name, table_schema, 'financials')
FROM information_schema.tables
WHERE table_name LIKE 'payment%' AND table_schema = 'public';
This routine we tested with inherited tables as well as regular tables and views and it repoints the indexes, constraints, rules and repoints the inherited child tables to the moved parent table. As a side benefit it corrects the definitions of views that reference moved tables to the new names. Note that we didn't need to explicitly move these in the above or rebuild the view because PostgreSQL is using the tables OID for referencing the actual table rather than the schema qualified name. The fact that views are automagically corrected may come as a surprise to many. This is possible we believe because PostgreSQL doesn't rely on the name of the table provided in the SQL definition of the view, but internally uses the OID of the table. This is different from say using Microsoft SQL Server or MySQL where renaming a table and so forth breaks your view.
This behavior of PostgreSQL is important to keep in mind, because it means you can't trick it by renaming a table and stuffing in a new table with the same original name (internally its using the OID of a table rather than the actual referenced name of the table). Your views will be magically changed to use the renamed bad table. To test this
SELECT oldname.field1, oldname.field2 ...
FROM newname As oldname
It is both great and scary at the same time. Sometimes you wish Postgres was not such a smart aleck, but for schema table movement, it is just what the doctor ordered.