Thread: Need help

Hi ,

We are facing some problems with the creation of tables of same name but
owned by different user .

We followed the following steps .

Lets say, we have a database DBTest and this database was created by the
user postgres.
We created  tables - Table1, Table2 and Table3 in it.
Now, by using the createuser command - one more database user is
created, say dbuser1.
Now, when I login as dbuser1 on the DBTest database, I can see all the
tables Table1, Table2 and Table3 by /dt command.
Even, I am able to create new tables ( i.e table with new names ) in the
same database DBTest but with the owner dbuser1.
Now, when I try to create the same table like Table1 ( which has been
created by the
postgress user previously ) as dbuser1 user  - the create table command
fails with the following o/p :

ERROR:  Relation 'Table1' already exists

My question  - is it possible in Postgres, to create the tables with
same name but with different users ?

i.e we create Table1 table both as postgres as well as dbuser1 .

Is it possible kindly let me know .

Thanks and Regards,
Shamik

On Tue, Jan 01, 2002 at 05:21:12PM +0530, Shamik Majumder wrote:
> My question  - is it possible in Postgres, to create the tables with
> same name but with different users ?

No.

--
Holger Krug
hkrug@rationalizer.com

Why do you want a database where two tables have the same name?  When
you do a "SELECT * from Table1" what table do you expect PostgreSQL to
use?

Now, that being said, it's possible to create temporary tables in
different connections with the same name.  These tables will dissapear
when the connection is terminated, however.  For example you could
have something like this:

conn1: CREATE TEMP TABLE foo (bar text);
conn2: CREATE TEMP TABLE foo (bar text);
conn1: INSERT INTO foo (bar) VALUES ('baz');
conn2: SELECT * from foo; [returns zero rows]
conn1: SELECT * from foo; [returns 'baz']

If you actually want two permanent tables with the same name, you are
going to have to put them in separate databases.  Otherwise use
temporary tables.

Jason

Shamik Majumder <shamik.majumder@wipro.com> writes:

> Hi ,
>
> We are facing some problems with the creation of tables of same name
> but owned by different user .
>
> We followed the following steps .
>
> Lets say, we have a database DBTest and this database was created by
> the user postgres.  We created tables - Table1, Table2 and Table3 in
> it.  Now, by using the createuser command - one more database user
> is created, say dbuser1.  Now, when I login as dbuser1 on the DBTest
> database, I can see all the tables Table1, Table2 and Table3 by /dt
> command.  Even, I am able to create new tables ( i.e table with new
> names ) in the same database DBTest but with the owner dbuser1.
> Now, when I try to create the same table like Table1 ( which has
> been created by the postgress user previously ) as dbuser1 user -
> the create table command fails with the following o/p :
>
> ERROR:  Relation 'Table1' already exists
>
> My question - is it possible in Postgres, to create the tables with
> same name but with different users ?
>
> i.e we create Table1 table both as postgres as well as dbuser1 .
>
> Is it possible kindly let me know .
>
> Thanks and Regards,
> Shamik
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Actually this concept is used in production environments or classes for
seperating student's school work.

I can see the need to create a Table1 under several different names.
Does postgresql have a schema concept like so:
dbuser1.Table1
postgres.Table1

This is how DB2 does it.

- Bill Cunningham
Technical Lead
Bally Gaming and Systems


Jason Earl wrote:

>Why do you want a database where two tables have the same name?  When
>you do a "SELECT * from Table1" what table do you expect PostgreSQL to
>use?
>
>Now, that being said, it's possible to create temporary tables in
>different connections with the same name.  These tables will dissapear
>when the connection is terminated, however.  For example you could
>have something like this:
>
>conn1: CREATE TEMP TABLE foo (bar text);
>conn2: CREATE TEMP TABLE foo (bar text);
>conn1: INSERT INTO foo (bar) VALUES ('baz');
>conn2: SELECT * from foo; [returns zero rows]
>conn1: SELECT * from foo; [returns 'baz']
>
>If you actually want two permanent tables with the same name, you are
>going to have to put them in separate databases.  Otherwise use
>temporary tables.
>
>Jason
>
>Shamik Majumder <shamik.majumder@wipro.com> writes:
>
>>Hi ,
>>
>>We are facing some problems with the creation of tables of same name
>>but owned by different user .
>>
>>We followed the following steps .
>>
>>Lets say, we have a database DBTest and this database was created by
>>the user postgres.  We created tables - Table1, Table2 and Table3 in
>>it.  Now, by using the createuser command - one more database user
>>is created, say dbuser1.  Now, when I login as dbuser1 on the DBTest
>>database, I can see all the tables Table1, Table2 and Table3 by /dt
>>command.  Even, I am able to create new tables ( i.e table with new
>>names ) in the same database DBTest but with the owner dbuser1.
>>Now, when I try to create the same table like Table1 ( which has
>>been created by the postgress user previously ) as dbuser1 user -
>>the create table command fails with the following o/p :
>>
>>ERROR:  Relation 'Table1' already exists
>>
>>My question - is it possible in Postgres, to create the tables with
>>same name but with different users ?
>>
>>i.e we create Table1 table both as postgres as well as dbuser1 .
>>
>>Is it possible kindly let me know .
>>
>>Thanks and Regards,
>>Shamik
>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Bill Cunningham <billc@ballydev.com> writes:

> Actually this concept is used in production environments or classes
> for seperating student's school work.
>
> I can see the need to create a Table1 under several different
> names. Does postgresql have a schema concept like so:
> dbuser1.Table1
> postgres.Table1
>
> This is how DB2 does it.

I understand that schemas are tentatively planned for 7.3, but they
are not currently available.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863