Re: [GENERAL] Explicitly adding a table to a schema.
On Fri, 2011-11-04 at 14:32 -0400, Demitri Muna wrote: > Hi, > > On Nov 4, 2011, at 2:09 PM, Adam Cornett wrote: > > > You can use ALTER TABLE > > (http://www.postgresql.org/docs/current/static/sql-altertable.html) to set > > the schema of existing tables: > > ALTER TABLE foo SET SCHEMA bar > > Thanks. I did try that, but that command moves the table to a different > schema, which is not what I'm trying to do. It struck me to try to move it to > another schema (where the definition then explicitly included the schema > prefix) and then move it back, but it still doesn't have the schema prefix. > pgAdmin doesn't add the schema name if the object is visible within your search_path. So, some objects will have their name prefixed with the schema name, and others won't. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Explicitly adding a table to a schema.
2011/11/4 Demitri Muna : > Hi, > > On Nov 4, 2011, at 2:09 PM, Adam Cornett wrote: > >> You can use ALTER TABLE >> (http://www.postgresql.org/docs/current/static/sql-altertable.html) to set >> the schema of existing tables: >> ALTER TABLE foo SET SCHEMA bar > > Thanks. I did try that, but that command moves the table to a different > schema, which is not what I'm trying to do. It struck me to try to move it to > another schema (where the definition then explicitly included the schema > prefix) and then move it back, but it still doesn't have the schema prefix. what you want? any table is in one schema - you can do some like simlink via view - and you can set a search_patch - a list of schemas that are acesable by default Regards Pavel > > Cheers, > Demitri > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Explicitly adding a table to a schema.
On Fri, Nov 4, 2011 at 2:32 PM, Demitri Muna wrote: > Hi, > > On Nov 4, 2011, at 2:09 PM, Adam Cornett wrote: > > > You can use ALTER TABLE ( > http://www.postgresql.org/docs/current/static/sql-altertable.html) to set > the schema of existing tables: > > ALTER TABLE foo SET SCHEMA bar > > Thanks. I did try that, but that command moves the table to a different > schema, which is not what I'm trying to do. It struck me to try to move it > to another schema (where the definition then explicitly included the schema > prefix) and then move it back, but it still doesn't have the schema prefix. > > Cheers, > Demitri > What you might be looking for then is the "search_path" http://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH when you specify an unqualified table, Postgres uses the search path to look for it, and when creating tables, unqualified tables go into 'public' which is the default search path. I'm not sure if you get pgadmin to add "public" to the create table statements, pg_dump might though. -Adam
Re: [GENERAL] Explicitly adding a table to a schema.
Hi, On Nov 4, 2011, at 2:09 PM, Adam Cornett wrote: > You can use ALTER TABLE > (http://www.postgresql.org/docs/current/static/sql-altertable.html) to set > the schema of existing tables: > ALTER TABLE foo SET SCHEMA bar Thanks. I did try that, but that command moves the table to a different schema, which is not what I'm trying to do. It struck me to try to move it to another schema (where the definition then explicitly included the schema prefix) and then move it back, but it still doesn't have the schema prefix. Cheers, Demitri -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Explicitly adding a table to a schema.
On Fri, Nov 4, 2011 at 1:57 PM, wrote: > Hi, > > I have a database where I wasn't explicitly using schemas when I started > it (i.e. everything was simply under "public"). I've since created several > schemas and renamed the "public" schema to something else. When I look at > the definitions (in PGAdmin III), the CREATE statement for the old tables > look like this: > > CREATE TABLE foo ( ... > > whereas for my newer tables the full schema path is there: > > CREATE TABLE myschema.bar ( ... > > Is there a way that the explicit schema can be added to my older tables? > The reason I want to do this is that I'm having a problem with external > code (SQLAlchemy) when trying to get foreign key information through > reflection-- the table doesn't seem to be found. > > Does this even make sense since the tables are definitely in the new > schema anyway? > > Cheers, > Demitri > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > You can use ALTER TABLE ( http://www.postgresql.org/docs/current/static/sql-altertable.html) to set the schema of existing tables: ALTER TABLE foo SET SCHEMA bar -Adam
