Re: ALTER TABLE schema SCHEMA TO new_schema? - Mailing list pgsql-hackers

From Tommi Maekitalo
Subject Re: ALTER TABLE schema SCHEMA TO new_schema?
Date
Msg-id 200212021355.49530.t.maekitalo@epgmbh.de
Whole thread Raw
In response to Re: ALTER TABLE schema SCHEMA TO new_schema?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
Am Sonntag, 1. Dezember 2002 06:47 schrieb Tom Lane:
> Joe Conway <mail@joeconway.com> writes:
> > Someone asked earlier about how to change a bunch of existing tables int
> > the PUBLIC schema to some other schema. For grins I tried:
> > regression=# update pg_class set relnamespace=556829 where relname =
> > 'foo' and relnamespace=2200;
> > UPDATE 1
> >
> > and it seemed to work fine (i.e. moved foo from schema public to schema
> > bar).
>
> But it didn't fix the pg_depend entries linking the table to its schema :-(
>
> > But it made me wonder if we shouldn't have:
> >    ALTER TABLE table SCHEMA TO new_schema
>
> I was thinking more along the lines of ALTER TABLE a.b RENAME TO x.y
>
> I don't see anything in the SQL spec about this; anyone know what
> precedent is in Oracle or other DBMSes?
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Here is, what DB2 has to offer:

DB2: Syntax
DB2:
DB2:            .-TABLE-.
DB2: >>-RENAME--+-------+--table-name--TO--new-table-identifier-----><
DB2:
DB2: Description
DB2:
DB2: |table-name
DB2: Names the existing table that is to be renamed. The name, including the
DB2: schema name, must identify a table that already exists in the database
DB2: (SQLSTATE 42704). It can be an alias identifying the table. It must not
DB2: be the name of a catalog table (SQLSTATE 42832), a summary table, a
DB2: typed table (SQLSTATE 42997), a nickname, or an object of other than
DB2: table or alias (SQLSTATE 42809).
DB2:
DB2: |new-table-identifier
DB2: |Specifies the new name for the table without a schema name. The |schema
DB2: name of the table-name is used to qualify the new name for the |table.
DB2: The qualified name must not identify a table, view, |or alias that
DB2: already exists in the database (SQLSTATE 42710).



It looks like it is not possible to move a table from one schema to another.
ALTER TABLE don't handle schemas either.

But I like the "RENAME a.x to b.x"-syntax.


Tommi

--
Dr. Eckhardt + Partner GmbH
http://www.epgmbh.de


pgsql-hackers by date:

Previous
From: "Nicolai Tufar"
Date:
Subject: Re: 7.4 Wishlist
Next
From: Prasanna Phadke
Date:
Subject: Re: 7.4 Wishlist