Thread: Schema Names

----- Original Message -----

From: Derrick Betts

To: pgsql-novice@postgresql.org

Sent: Friday, July 28, 2006 9:59 PM

Subject: [NOVICE] Schema Names

In the public schema, when a table is created the table name does not get pre-pended by the schema name.  For example:

        CREATE TABLE new_table (column_name varchar);

 

    SELECT column_name FROM new_table;  This returns a result as expected.

 

However, if I create a new schema, it would seem that the only way to access a table within the new schema is to pre-pend the table name with the schema name:

    CREATE SCHEMA new_schema;

        CREATE TABLE new_table (column_name varchar);

 

    SET search_path TO new_schema;

    SELECT column_name FROM new_table;  This returns an error saying there is no such table as new_table.  What has happened is that when I created the new table the table name automatically became:  new_schema.new_table  Therefore the only way to query the new table is this way:

    SELECT column_name FROM new_schema.new_table;

 

Is there a way to "turn off" this automated behavior that pre-pends every table, etc. that is created with the schema name?  I am using pgAdmin III as the interface for creating the schemas.  I am also using version 8.1.  Is this a PostgreSQL behavior or a pgAdmin II behavior?

 

Thank you,

Derrick

"Derrick Betts" <derrick@blueaxis.com> writes:
> However, if I create a new schema, it would seem that the only way to =
> access a table within the new schema is to pre-pend the table name with =
> the schema name:
>     CREATE SCHEMA new_schema;
>         CREATE TABLE new_table (column_name varchar);

>     SET search_path TO new_schema;
>     SELECT column_name FROM new_table;  This returns an error saying =
> there is no such table as new_table.

That's because you created new_table in the public schema.  I think you
are confusing

    CREATE SCHEMA new_schema;
    CREATE TABLE new_table (column_name varchar);

(two independent commands, and the second one creates new_table in whatever
schema is current according to search_path) with

    CREATE SCHEMA new_schema
        CREATE TABLE new_table (column_name varchar);

which per SQL spec makes new_schema and then creates new_table within
it.  That semicolon makes a lot of difference...

            regards, tom lane