Re: Oracle to Postgres Migration

I have worked on Federal, State and commercial projects and this is how any database object is referenced.
Not prefixing schema name along with the database object name will lead to several chaos situation in my opinion too.
Thanks,
Sarwar

________________________________
From: Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com>
Sent: Thursday, February 1, 2024 10:52 AM
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; Kalyani Maity <bimal(dot)af2020(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Oracle to Postgres Migration

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 Best Practice).

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

Just my $0.02

-----Original Message-----
From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Sent: Thursday, February 1, 2024 4:38 AM
To: Kalyani Maity <bimal(dot)af2020(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)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