public inbox for [email protected]
help / color / mirror / Atom feedSame Table Name - 2 Owners
6+ messages / 5 participants
[nested] [flat]
* Same Table Name - 2 Owners
@ 2024-10-04 22:09 Sam Stearns <[email protected]>
2024-10-04 22:17 ` Re: Same Table Name - 2 Owners David G. Johnston <[email protected]>
2024-10-04 22:21 ` Re: Same Table Name - 2 Owners Holger Jakobs <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Sam Stearns @ 2024-10-04 22:09 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>; +Cc: Peter Garza <[email protected]>; Henry Ashu <[email protected]>
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
[image: DAT]
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Same Table Name - 2 Owners
2024-10-04 22:09 Same Table Name - 2 Owners Sam Stearns <[email protected]>
@ 2024-10-04 22:17 ` David G. Johnston <[email protected]>
2024-10-04 22:22 ` Re: Same Table Name - 2 Owners Tom Lane <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2024-10-04 22:17 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
On Friday, October 4, 2024, Sam Stearns <[email protected]> wrote:
>
> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Same Table Name - 2 Owners
2024-10-04 22:09 Same Table Name - 2 Owners Sam Stearns <[email protected]>
2024-10-04 22:17 ` Re: Same Table Name - 2 Owners David G. Johnston <[email protected]>
@ 2024-10-04 22:22 ` Tom Lane <[email protected]>
2024-10-05 07:07 ` Re: Same Table Name - 2 Owners Guillaume Lelarge <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2024-10-04 22:22 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Sam Stearns <[email protected]>; Pgsql-admin <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Friday, October 4, 2024, Sam Stearns <[email protected]> wrote:
>> 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.
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Same Table Name - 2 Owners
2024-10-04 22:09 Same Table Name - 2 Owners Sam Stearns <[email protected]>
2024-10-04 22:17 ` Re: Same Table Name - 2 Owners David G. Johnston <[email protected]>
2024-10-04 22:22 ` Re: Same Table Name - 2 Owners Tom Lane <[email protected]>
@ 2024-10-05 07:07 ` Guillaume Lelarge <[email protected]>
2024-10-07 15:36 ` Re: Same Table Name - 2 Owners Sam Stearns <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Guillaume Lelarge @ 2024-10-05 07:07 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; Sam Stearns <[email protected]>; Pgsql-admin <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
Hello,
Le sam. 5 oct. 2024 à 00:22, Tom Lane <[email protected]> a écrit :
> "David G. Johnston" <[email protected]> writes:
> > On Friday, October 4, 2024, Sam Stearns <[email protected]> wrote:
> >> 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.
>
> 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.
>
>
+1
Great article on this topic:
https://stormatics.tech/blogs/transitioning-from-oracle-to-postgresql-understanding-the-concept-of-s...
Regards.
--
Guillaume.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Same Table Name - 2 Owners
2024-10-04 22:09 Same Table Name - 2 Owners Sam Stearns <[email protected]>
2024-10-04 22:17 ` Re: Same Table Name - 2 Owners David G. Johnston <[email protected]>
2024-10-04 22:22 ` Re: Same Table Name - 2 Owners Tom Lane <[email protected]>
2024-10-05 07:07 ` Re: Same Table Name - 2 Owners Guillaume Lelarge <[email protected]>
@ 2024-10-07 15:36 ` Sam Stearns <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Sam Stearns @ 2024-10-07 15:36 UTC (permalink / raw)
To: Guillaume Lelarge <[email protected]>; +Cc: Tom Lane <[email protected]>; David G. Johnston <[email protected]>; Pgsql-admin <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
Thank you all for the help! We're testing this out now with some Oracle
imports into Postgres.
Sam
On Sat, Oct 5, 2024 at 12:07 AM Guillaume Lelarge <[email protected]>
wrote:
> Hello,
>
> Le sam. 5 oct. 2024 à 00:22, Tom Lane <[email protected]> a écrit :
>
>> "David G. Johnston" <[email protected]> writes:
>> > On Friday, October 4, 2024, Sam Stearns <[email protected]> wrote:
>> >> 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.
>>
>> 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.
>>
>>
> +1
>
> Great article on this topic:
> https://stormatics.tech/blogs/transitioning-from-oracle-to-postgresql-understanding-the-concept-of-s...
>
> Regards.
>
>
> --
> Guillaume.
>
--
*Samuel Stearns*
Lead Database Administrator
*c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com
[image: DAT]
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Same Table Name - 2 Owners
2024-10-04 22:09 Same Table Name - 2 Owners Sam Stearns <[email protected]>
@ 2024-10-04 22:21 ` Holger Jakobs <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Holger Jakobs @ 2024-10-04 22:21 UTC (permalink / raw)
To: [email protected]
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>/
>
>
> Is it possible to do this in Postgres?
>
> --
>
> *Samuel Stearns*
> Lead Database Administrator
> *c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com
>
> DAT
> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link;
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
Attachments:
[application/pgp-signature] OpenPGP_signature (203B, 3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-10-07 15:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-04 22:09 Same Table Name - 2 Owners Sam Stearns <[email protected]>
2024-10-04 22:17 ` David G. Johnston <[email protected]>
2024-10-04 22:22 ` Tom Lane <[email protected]>
2024-10-05 07:07 ` Guillaume Lelarge <[email protected]>
2024-10-07 15:36 ` Sam Stearns <[email protected]>
2024-10-04 22:21 ` Holger Jakobs <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox