By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

Move a table to another schema

P: n/a
I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":

UPDATE pg_class
SET relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'x001')
FROM pg_namespace
WHERE pg_class.relname = 'zxc' AND
pg_namespace.nspname = 'public' AND
pg_class.relnamespace = pg_namespace.oid

Has anyone else addressed this before? Recommendations?

Thanks, L.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Mon, Nov 17, 2003 at 04:05:04PM +0000, Lee Kindness wrote:
I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":


You have to move all indexes, constraints, the type, etc too.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"There was no reply" (Kernel Traffic)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2

P: n/a

I would imagine the safest way would be to recreate the table in the
new schema and do a INSERT INTO ...SELECT * FROM ....
Not elegant, but perfectly safe. You mess with the pg_* catalogs at
your own risk.

On Nov 17, 2003, at 11:05 AM, Lee Kindness wrote:
I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":

UPDATE pg_class
SET relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'x001')
FROM pg_namespace
WHERE pg_class.relname = 'zxc' AND
pg_namespace.nspname = 'public' AND
pg_class.relnamespace = pg_namespace.oid

Has anyone else addressed this before? Recommendations?

Thanks, L.

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #3

P: n/a
When I have contemplated doing this, I figured I would just dump the
database, then alter the search patch and put in the schema name I wanted
instead of public and theoretically the tables and data should be recreated
in the new schema. I would create the new schema first.

Julie
I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #4

P: n/a
Shridhar,

Shridhar Daithankar writes:
Lee Kindness wrote:
I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":

Why not just rename the schema itself? You can recreate a public
schema later..:-)

Simple, isn't it? (Unless public is a specieal schema)


Thanks Shridhar - it's good when someone has a different angle on
things!

Of course I'm using 7.3, so there is no ALTER SCHEMA - but the
catalogue magic required to rename a single schema will be a lot less
than renaming 100s of tables, indices and views!

L.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #5

P: n/a
Julie May wrote:
When I have contemplated doing this, I figured I would just dump the
database, then alter the search patch and put in the schema name I wanted
instead of public and theoretically the tables and data should be recreated
in the new schema. I would create the new schema first.

Julie

I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":


Why not just rename the schema itself? You can recreate a public schema later..:-)

Simple, isn't it? (Unless public is a specieal schema)

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.