Subject: Same Table Name - 2 Owners

From: Sam Stearns <[email-masked]>
Date: Fri, 4 Oct 2024 15:09:59 -0700

Howdy,

In Oracle, I have a table name that is the same name between 2 different
owners but completely different DDL:

*SQL> select owner, object_name, object_type from dba_objects where
object_name = 'SUBSCRIPTIONS' and object_type = 'TABLE';OWNER
 OBJECT_NAME          OBJECT_TYPE---------- --------------------
-----------------------INTERFACE  SUBSCRIPTIONS        TABLEORACLE
SUBSCRIPTIONS        TABLESQL>*

Is it possible to do this in Postgres?

-- 

*Samuel Stearns*
Lead Database Administrator
*c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 4 Oct 2024 15:17:03 -0700

> Is it possible to do this in Postgres?

Within PostgreSQL, two objects can have the same name if they are of
different types (note, relations is a fairly broad type category) or they
exist in different schemas.

David J.

---

From: Holger Jakobs <[email-masked]>
Date: Sat, 5 Oct 2024 00:21:27 +0200

Am 05.10.24 um 00:09 schrieb Sam Stearns:
> Howdy,
>
> In Oracle, I have a table name that is the same name between 2 
> different owners but completely different DDL:
>
>
> /SQL> select owner, object_name, object_type from dba_objects where 
> object_name = 'SUBSCRIPTIONS' and object_type = 'TABLE';
>
> OWNER      OBJECT_NAME          OBJECT_TYPE
> ---------- -------------------- -----------------------
> INTERFACE  SUBSCRIPTIONS        TABLE
> ORACLE     SUBSCRIPTIONS        TABLE
>
> SQL>/
>
>
> DAT 
In Oracle, owner is synonomous with a schema, they are coupled 1:1. So 
don't be fooled, the two tables are in two different schemas.

If you want to do this in PostgreSQL, just create two schemas and put 
two tables with the same name (but different properties) into each of 
them, respectively.

In PostgreSQL, users (actually login roles) and schemas aren't tied to 
each other. You are free to name them as you wish.

Regards,
Holger

---

From: Tom Lane <[email-masked]>
Date: Fri, 04 Oct 2024 18:22:28 -0400

> exist in different schemas.

The Oracle case probably also works by putting the tables in different
schemas.  I recall hearing that Oracle identifies "owner" with
"schema" much more closely than we do.

			regards, tom lane

---

From: Guillaume Lelarge <[email-masked]>
Date: Sat, 5 Oct 2024 09:07:05 +0200

Hello,

> "schema" much more closely than we do.
+1

Great article on this topic:
https://stormatics.tech/blogs/transitioning-from-oracle-to-postgresql-understanding-the-concept-of-schema

Regards.

-- 
Guillaume.

---

From: Sam Stearns <[email-masked]>
Date: Mon, 7 Oct 2024 08:36:30 -0700

Thank you all for the help!  We're testing this out now with some Oracle
imports into Postgres.

Sam

-- 

*Samuel Stearns*