Обсуждение: Explicitly adding a table to a schema.

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
havinga problem with external code (SQLAlchemy) when trying to get foreign key information through reflection-- the
tabledoesn't seem to be found. 

Does this even make sense since the tables are definitely in the new schema anyway?

Cheers,
Demitri

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
existingtables: 
> 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
struckme to try to move it to another schema (where the definition then explicitly included the schema prefix) and then
moveit back, but it still doesn't have the schema prefix. 

Cheers,
Demitri

2011/11/4 Demitri Muna <thatsanicehatyouhave@mac.com>:
> 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
existingtables: 
>> 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.
Itstruck me to try to move it to another schema (where the definition then explicitly included the schema prefix) and
thenmove 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
>

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
existingtables: 
> > 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.
Itstruck me to try to move it to another schema (where the definition then explicitly included the schema prefix) and
thenmove 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