Re: Moving a table to a different schema

> On Thu, 2003-05-29 at 09:14, Jim C. Nasby wrote:
>
>> Is there a command to move an existing table to a different schema?
>
> WARNING: The following works for me, but there might be other
> ramifications that I'm not aware of.
>
> Imagine moving unison.locus to public.locus.  First:
>

Somebody earlier suggested using pg_dump. Up to this point I've only used
pg_dump to dump the entire schema and data for backup and occasionally
modified the output for changes to table definitions (during database
design/development, not in a production environment, however).

What I recently "discovered" was using pg_dump to dump only a single
table. It produces output the lets you recreate the table, reload data,
and recreate constraints and triggers, not just the table definition. You
could easily modifiy that output and change any occurances of the old
schema name to the new schema name.

I at first thought I had a problem with that because the views and
associated rules would be lost when you dropped the old table prior to
reload from the modified script. But then I tried doing a pg_dump for a
single table, but instead of a table I specified a view defined on the
subject table. The ouput of pg_dump for a view includes any rules defined
on the view as well as its definition, so you don't lose those. The only
thing you might lose is foreign key constraints on OTHER  tables that
reference the subject table.

~Berend Tober