Re: ALTER TABLE schema SCHEMA TO new_schema?

Am Sonntag, 1. Dezember 2002 06:47 schrieb Tom Lane:
> Joe Conway <mail(at)joeconway(dot)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(at)postgresql(dot)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