Thread: ALTER TABLE schema SCHEMA TO new_schema?
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
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
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
> > 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
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
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
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
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
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
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
> 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
> 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
> 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
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
> > 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