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=# select oid,* from pg_namespace ;
   oid   |  nspname   | nspowner | nspacl
--------+------------+----------+--------
      11 | pg_catalog |        1 | {=U}
      99 | pg_toast   |        1 | {=}
    2200 | public     |        1 | {=UC}
   16766 | pg_temp_1  |        1 |
  556829 | bar        |        1 |
(5 rows)

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 made me wonder if we shouldn't have:

   ALTER TABLE table SCHEMA TO new_schema

as a supported method to do this?

Joe


Sat, 30 Nov 2002 21:27:44 -0800
Joe Conway <mail@joeconway.com>
ALTER TABLE schema SCHEMA TO new_schema?
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

Sun, 01 Dec 2002 00:47:53 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: ALTER TABLE schema SCHEMA TO new_schema? 
Tom Lane wrote:
> 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 :-(

Yeah, I knew there was something I was forgetting. That's why I didn't 
actually offer it up as a solution to anyone.

>>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?

Good question. I can't find anything in the Oracle docs indicating it is even 
possible. We should probably just go with your suggestion. Anything else 
beyond the relnamespace and pg_depend entries that need to be dealt with?

Joe



Sat, 30 Nov 2002 22:15:17 -0800
Joe Conway <mail@joeconway.com>
Re: ALTER TABLE schema 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?
>
> Good question. I can't find anything in the Oracle docs indicating it is
even
> possible. We should probably just go with your suggestion. Anything else
> beyond the relnamespace and pg_depend entries that need to be dealt with?

What about sequences for serial columns?  What about views or types that
depend on the table?

Chris


Sun, 1 Dec 2002 11:21:36 -0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: ALTER TABLE schema SCHEMA TO new_schema?
Christopher Kings-Lynne wrote:
>>possible. We should probably just go with your suggestion. Anything else
>>beyond the relnamespace and pg_depend entries that need to be dealt with?
> 
> What about sequences for serial columns?  What about views or types that
> depend on the table?
> 

Yeah, good point. I think properly dealing with the pg_depends issues will 
catch anything of that nature, but what to do with them?

Probably should move dependent type, constraint, index entries to the same new 
namespace. We might want to move related sequences, but I'm not sure we'd want 
to do that silently, since the sequence could be in use for other tables as 
well. And we should probably restrict the change if there are dependent 
functions or views. Does this capture the issues?

Joe


Sun, 01 Dec 2002 14:52:11 -0800
Joe Conway <mail@joeconway.com>
Re: ALTER TABLE schema SCHEMA TO new_schema?
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

Mon, 2 Dec 2002 13:55:49 +0100
Tommi Maekitalo <t.maekitalo@epgmbh.de>
Re: ALTER TABLE schema SCHEMA TO new_schema?
Joe Conway wrote:
> Christopher Kings-Lynne wrote:
> 
>>> possible. We should probably just go with your suggestion. Anything else
>>> beyond the relnamespace and pg_depend entries that need to be dealt 
>>> with?
>>
>>
>> What about sequences for serial columns?  What about views or types that
>> depend on the table?
>>
> 
> Yeah, good point. I think properly dealing with the pg_depends issues 
> will catch anything of that nature, but what to do with them?
> 
> Probably should move dependent type, constraint, index entries to the 
> same new namespace. We might want to move related sequences, but I'm not 
> sure we'd want to do that silently, since the sequence could be in use 
> for other tables as well. And we should probably restrict the change if 
> there are dependent functions or views. Does this capture the issues?
> 

Why not just leave the sequence and types in the original schema and 
make sure the table refers to them _there_?  We just need to make sure 
we have schema qualified references to the sequences and types.

Indexes, triggers (and constraints), toast tables etc. are related to 
just one table so they can migrate together, I think.

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9


Mon, 02 Dec 2002 10:35:34 -0500
Fernando Nasser <fnasser@redhat.com>
Re: ALTER TABLE schema SCHEMA TO new_schema?
Tommi Maekitalo wrote:
> 
> But I like the "RENAME a.x to b.x"-syntax.
> 

And we would not be creating a new syntax, but just changing the 
semantics of an existing one to be schema-aware.  Still an extension 
that should be noted in the docs, but less intrusive.



-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9


Mon, 02 Dec 2002 10:37:38 -0500
Fernando Nasser <fnasser@redhat.com>
Re: ALTER TABLE schema SCHEMA TO new_schema?
Fernando Nasser wrote:
> Why not just leave the sequence and types in the original schema and 
> make sure the table refers to them _there_?  We just need to make sure 
> we have schema qualified references to the sequences and types.

Well, the type entry for the relation *is* related to just one table, so I'd 
be inclined to move it also. But leaving the sequence alone might be the best 
thing to do. Although, I think sequences created via SERIAL are dropped with 
their referencing table now, aren't they?

test=# create table myserial(id serial);
NOTICE:  CREATE TABLE will create implicit sequence 'myserial_id_seq' for 
SERIAL column 'myserial.id'
CREATE TABLE
test=# \ds myserial_id_seq
                List of relations
  Schema |      Name       |   Type   |  Owner
--------+-----------------+----------+----------
  public | myserial_id_seq | sequence | postgres
(1 row)

test=# drop table myserial;
DROP TABLE
test=# \ds myserial_id_seq
No matching relations found.

Maybe that's an argument that they ought to also move to the new schema when 
the dependency exists.

> Indexes, triggers (and constraints), toast tables etc. are related to 
> just one table so they can migrate together, I think.

I agree.

Joe



Mon, 02 Dec 2002 09:28:22 -0800
Joe Conway <mail@joeconway.com>
Re: ALTER TABLE schema SCHEMA TO new_schema?
I wonder if the sequences created by SERIAL should not be going into a 
pg_sequence schema and protected like the toast tables are.

One could still share sequences by explicitly creating them and using a 
DEFAULT clause with nextval().

We could even stop printing that annoying NOTICE ;-)

Regards,
Fernando

Joe Conway wrote:
> Fernando Nasser wrote:
> 
>> Why not just leave the sequence and types in the original schema and 
>> make sure the table refers to them _there_?  We just need to make sure 
>> we have schema qualified references to the sequences and types.
> 
> 
> Well, the type entry for the relation *is* related to just one table, so 
> I'd be inclined to move it also. But leaving the sequence alone might be 
> the best thing to do. Although, I think sequences created via SERIAL are 
> dropped with their referencing table now, aren't they?
> 
> test=# create table myserial(id serial);
> NOTICE:  CREATE TABLE will create implicit sequence 'myserial_id_seq' 
> for SERIAL column 'myserial.id'
> CREATE TABLE
> test=# \ds myserial_id_seq
>                List of relations
>  Schema |      Name       |   Type   |  Owner
> --------+-----------------+----------+----------
>  public | myserial_id_seq | sequence | postgres
> (1 row)
> 
> test=# drop table myserial;
> DROP TABLE
> test=# \ds myserial_id_seq
> No matching relations found.
> 
> Maybe that's an argument that they ought to also move to the new schema 
> when the dependency exists.
> 
>> Indexes, triggers (and constraints), toast tables etc. are related to 
>> just one table so they can migrate together, I think.
> 
> 
> I agree.
> 
> Joe
> 
> 


-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9


Mon, 02 Dec 2002 13:30:04 -0500
Fernando Nasser <fnasser@redhat.com>
Re: ALTER TABLE schema SCHEMA TO new_schema?
> We could even stop printing that annoying NOTICE ;-)

Agreed with this part :)

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
02 Dec 2002 14:08:24 -0500
Rod Taylor <rbt@rbt.ca>
Re: ALTER TABLE schema SCHEMA TO new_schema?
> Yeah, good point. I think properly dealing with the pg_depends issues will
> catch anything of that nature, but what to do with them?
>
> Probably should move dependent type, constraint, index entries to the same
new
> namespace. We might want to move related sequences, but I'm not sure we'd
want
> to do that silently, since the sequence could be in use for other tables
as
> well. And we should probably restrict the change if there are dependent
> functions or views. Does this capture the issues?

Why just restrict them to moving tables?  What if someone wants to move a
function or an aggregate to another schema?

What if they want to copy it?

Chris


Mon, 2 Dec 2002 12:03:58 -0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: ALTER TABLE schema SCHEMA TO new_schema?
> Why just restrict them to moving tables?  What if someone wants to move a
> function or an aggregate to another schema?
> 
> What if they want to copy it?

Copying might be tricky, but I'd be happy to help with moving everything
else around.  Though I don't think sequences can move (until we can
properly track their dependencies) but everything else should be able
to.

Copy is another story all together.  But I'd like a

CREATE SCHEMA ... AS COPY <schemaname>;

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
02 Dec 2002 15:34:42 -0500
Rod Taylor <rbt@rbt.ca>
Re: ALTER TABLE schema SCHEMA TO new_schema?
Rod Taylor wrote:
>>Why just restrict them to moving tables?  What if someone wants to move a
>>function or an aggregate to another schema?
>>
>>What if they want to copy it?
> 
> 
> Copying might be tricky, but I'd be happy to help with moving everything
> else around.  Though I don't think sequences can move (until we can
> properly track their dependencies) but everything else should be able
> to.
> 
> Copy is another story all together.  But I'd like a
> 
> CREATE SCHEMA ... AS COPY <schemaname>;
> 

Wouldn't it be better to use pg_dump/pg_restore for that?

If we could ask for just oen/some of the non-system schemas to be dumped 
it would be easy to restore it as another or even move it to another 
database.  And one could dump only the schema or schema+data, as needed.
Of course, dependencies would have to be handled as objects can refer to 
objects in other schemas.

-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9


Tue, 03 Dec 2002 11:20:22 -0500
Fernando Nasser <fnasser@redhat.com>
Re: ALTER TABLE schema SCHEMA TO new_schema?
> > Copy is another story all together.  But I'd like a
> > 
> > CREATE SCHEMA ... AS COPY <schemaname>;
> > 
> 
> Wouldn't it be better to use pg_dump/pg_restore for that?

Perhaps..  But I'd really like to see some of these types of abilities
added to pg_admin.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
03 Dec 2002 11:51:49 -0500
Rod Taylor <rbt@rbt.ca>
Re: ALTER TABLE schema SCHEMA TO new_schema?