Postgres Pro
Downloads
Home   >   mailing lists

Thread: Changing schemas

Changing schemas

From
Naz Gassiep
Date:
    Is there a way to change the schema that all objects are in?
Essentially I want to move everything currently in the database into
public rather than having the complex schemas that I have at the moment.
They are unnecessary and the DB complexity is trivial, so using schema
partitioning is more trouble than it is worth. Is there a way I can do
this? I tried editing a dump, but that has proven to be a little
difficult, as I am not a master of regex.

If I can do it from within the DB before I do the dump that'd be easier.
Is there a way to do this from within the DB?

Thanks,
- Naz.

Re: Changing schemas

From
Sim Zacks
Date:
ALTER TABLE name
     SET SCHEMA new_schema


Naz Gassiep wrote:
>    Is there a way to change the schema that all objects are in?
> Essentially I want to move everything currently in the database into
> public rather than having the complex schemas that I have at the moment.
> They are unnecessary and the DB complexity is trivial, so using schema
> partitioning is more trouble than it is worth. Is there a way I can do
> this? I tried editing a dump, but that has proven to be a little
> difficult, as I am not a master of regex.
>
> If I can do it from within the DB before I do the dump that'd be easier.
> Is there a way to do this from within the DB?
>
> Thanks,
> - Naz.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

Re: Changing schemas

From
Sim Zacks
Date:
Try renaming the schema. alter schema schemaname rename to newname;
then if you want the old schema back create a new one.

If there are already stuff in the schema you want to move to then
write a quick function using a for loop with the pg_class table
to write the alter table statement for anything that has the
schema you are looking for.

Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

________________________________________________________________________________

I need a single command to change ALL objects

Something like "alter table (select name from tables where schema =
'foo') set schema bar"

Sim Zacks wrote:
> ALTER TABLE name
>     SET SCHEMA new_schema
>
>
> Naz Gassiep wrote:
>>    Is there a way to change the schema that all objects are in?
>> Essentially I want to move everything currently in the database into
>> public rather than having the complex schemas that I have at the
>> moment. They are unnecessary and the DB complexity is trivial, so
>> using schema partitioning is more trouble than it is worth. Is there
>> a way I can do this? I tried editing a dump, but that has proven to
>> be a little difficult, as I am not a master of regex.
>>
>> If I can do it from within the DB before I do the dump that'd be
>> easier. Is there a way to do this from within the DB?
>>
>> Thanks,
>> - Naz.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>       subscribe-nomail command to majordomo@postgresql.org so that your
>>       message can get through to the mailing list cleanly
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>