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.albe@xxxxxxxxxxx> Sent: Thursday, February 1, 2024 4:38 AM To: Kalyani Maity <bimal.af2020@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx 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
