I have a database which over many years and clients has grown to about 140 tables with attendant triggers , rules and views. Having transferred to the postgresql community from another database provider the design did not include the use of schema. During a recent review it struck me just how useful the schema are as a management tool and I would like to now separate the various sub-sections in this database under different schema. My problem. The time and the risk of error. There does not appear to be a simple command like: alter table public.xxx rename to yyy.xxx; or transfer [table][view][rule] public.abc to schema yyy; As this is probably a common issue many have faced as they upgrade their database designs can someone please advise on the recommended method? Bear in mind that their are a number of client systems out there that need the same treatment. I can see perhaps doing a dump to plain text and then editing the schema references..... but the opportunity for error would be pretty big and the editing task would have to be repeated many times. if a new command were available to transfer between schemas then the wishlist would eventually extend to ... transfer [table] [view] [rule] public.* to schema yyy; Where * would mean all entries of the selected type or since I have used prefixes to table names to indicate the modules in which they are used the ideal in my case would be.. transfer [table][view][rule] public.xxx_* to schema yyy; which would transfer all objects of the selected type whoose names match "xxx_*" to schema yyy I am not sure how these suggestions sit with the intended use of schemas and will appreciate any help possible in effecting the transfer. Much thanks Richard ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
