Обсуждение: Oracle to Postgres Migration

On Thu, 2024-02-01 at 16:20 +0530, Kalyani Maity wrote:
> I have one scenario where one synonym created as below in oracle DB:
>
> create synonym 'schema1.procedure1' for 'schema2.procedure1'
>
> procedure1 only exist in schema2.
>
> I have migrated both schema 1 and schema 2 in postgres.
>
> How to create this synonym in postgres.

You don't.  Instead, you set "search_path" to include both schemas.

Yours,
Laurenz Albe

Hi,

I am doing an oracle to postgres migration.

I have one scenario where one synonym created as below in oracle DB:

create synonym 'schema1.procedure1' for 'schema2.procedure1'

procedure1 only exist in schema2.

I have migrated both schema 1 and schema 2 in postgres.

How to create this synonym in postgres.

Thanks.

I disagree a little with this.  Setting search_path to fix non schema qualified SQL, is not a Best Practice.

You CAN do this and it will work, but it CAN cause trouble if your database has things of the same name. (again a not
BestPractice).
 

My personal opinion on this, is to correct your SQL to include the schema qualified syntax (schema.whatever.)  This way
youare always 100% sure of what you are doing.
 

Just my $0.02


-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at> 
Sent: Thursday, February 1, 2024 4:38 AM
To: Kalyani Maity <bimal.af2020@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Oracle to Postgres Migration

On Thu, 2024-02-01 at 16:20 +0530, Kalyani Maity wrote:
> I have one scenario where one synonym created as below in oracle DB:
> 
> create synonym 'schema1.procedure1' for 'schema2.procedure1'
> 
> procedure1 only exist in schema2.
> 
> I have migrated both schema 1 and schema 2 in postgres.
> 
> How to create this synonym in postgres.

You don't.  Instead, you set "search_path" to include both schemas.

Yours,
Laurenz Albe