Re: How start using schemas for existing database

On Wed, 5 Feb 2003, Berend Tober wrote:

> So I'm convinced that using the new support for multiple schemas in a database
> is a good thing. I've got a database with scores of tables currently residing
> within the public schema. I've decided which tables I want to put in
> application-specific schemas and which to leave in public or maybe put in some
> shared schema created for that purpose of being used by mulitple applications.
>
> What is a good, efficient way to get the tables distributed into the schemas?
> It would be too good to be true if I could simply drag and drop tables into
> new schemas using pgAdminII, but think that is not possible.
>
> Do I have to do a pg_dump, and then edit the resulting script manually to
> prepend table names with the intended schema name? How about all the related
> indexes and relational integrity triggers? Manually editing the script file
> for a large database seems like a process doomed to introduce many
> opportunities for errors. What is the best way to handle this?
>

I would have thought for things such as tables the easiest way to do it would
be to update the schema for the appropiate entries in pg_class. However, I've
not tried it so I've no idea if it would break triggers or not. First guess I
would say not but that could be completely wrong.


--
Nigel J. Andrews