How to create a synonym for a table in PostgreSQL

Question

I am migrating this Oracle command to PostgreSQL:

CREATE SYNONYM &user..emp FOR &schema..emp;

Please suggest to me how I can migrate the above command.

Answer 1

You don't need synonyms.

There are two approaches:

1. using the schema search path:

   ALTER DATABASE xyz SET search_path = schema1, schema2, ...;
   

   Put the schema that holds the table on the search_path of the database (or user), then it can be used without schema qualification.

2. using a view:

   CREATE VIEW dest_schema.tab AS SELECT * FROM source_schema.tab;
   

The first approach is good if you have a lot of synonyms for objects in the same schema.

Answer 2

PostgreSQL does not support SYNOSYM or ALIAS. Synonym is a non SQL 2003 feature implemented by Microsoft SQL 2005 (I think). While it does provide an interesting abstraction, but due to lack of relational integrity, it can be considered a risk.

That is, you can create a synonym, advertise it to you programmers, the code is written around it, including stored procedures, then one day the backend of this synonym (or link or pointer) is changed/deleted/etc leading to a run time error. I don't even think a prepare would catch that.

It is the same trap as the symbolic links in unix and null pointers in C/C++.

But rather create a DB view and it is already updateable as long as it contains one table select only.

CREATE VIEW schema_name.synonym_name AS SELECT * FROM schema_name.table_name;