A synonym is an identifier that can be used to reference another database object in a SQL statement. A synonym is useful in the scenarios where a database object requires full qualification by schema name to be correctly referenced in a SQL statement. A synonym defined for that object simplifies the reference to a single and unqualified name.

PolarDB for PostgreSQL(Compatible with Oracle) support synonyms for:

  • Tables
  • Views
  • Materialized views
  • Sequences
  • Procedures
  • Functions
  • Types
  • Objects that are accessible through a database link
  • Other synonyms

The referenced schema or the referenced object may exist at the time when you create the synonym. A synonym may reference a non-existent object or schema. A synonym is invalid if you drop the referenced object or schema. You must explicitly drop a synonym to remove the synonym.

Similar to any other schema object, PolarDB for PostgreSQL(Compatible with Oracle) use the search path to resolve unqualified synonym names. If you have two synonyms with the same name, an unqualified reference to a synonym resolves to the first synonym with the specified name in the search path. If public is in your search path, you can reference a synonym in the schema without qualifying that name.

When a PolarDB for PostgreSQL(Compatible with Oracle) executes a SQL statement, the permissions of the current user are checked based on the underlying database object of the synonym. If the user does not have the proper permissions for that object, the SQL statement fails.

Create a synonym

Use the CREATE SYNONYM statement to create a synonym. The statement has the following syntax:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]syn_name
       FOR object_schema.object_name[@dblink_name];
Table 1. Parameters
ParameterDescription
syn_nameThe name of the synonym. A synonym name must be unique within a schema.
schemaThe name of the schema where the synonym is located. If you do not specify a schema name, the synonym is created in the first existing schema in your search path.
object_nameThe name of the object.
object_schemaThe name of the schema where the object is located.
dblink_nameThe name of the database link through which a target object may be accessed.

You must include the REPLACE clause to replace an existing synonym definition with a new synonym definition.

You must include the PUBLIC clause to create the synonym in the public schema. Compatible with Oracle databases, the CREATE PUBLIC SYNONYM statement creates a synonym that is located in the public schema:

CREATE [OR REPLACE] PUBLIC SYNONYM syn_name FOR object_schema.object_name;

The following example is a shorthand version:

CREATE [OR REPLACE] SYNONYM public.syn_name FOR object_schema.object_name;

The following example is used to create a synonym named personnel that references the polardb.emp table.

CREATE SYNONYM personnel FOR polardb.emp;

Unless the synonym is schema qualified in the CREATE SYNONYM statement, the synonym is created in the first existing schema in your search path. You can view your search path by executing the following statement:

SHOW SEARCH_PATH;

      search_path
-----------------------
 development,accounting
(1 row)

In this example, if a schema named development does not exist, the synonym is created in the schema named accounting.

The emp table in the polardb schema can be referenced in any DDL or DML SQL statement, by using the personnel synonym:

INSERT INTO personnel VALUES (8142,'ANDERSON','CLERK',7902,'17-DEC-06',1300,NULL,20);

SELECT * FROM personnel;

 empno |  ename   |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+----------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH    | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN    | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD     | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES    | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN   | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE    | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK    | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT    | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING     | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER   | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS    | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES    | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD     | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER   | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
  8142 | ANDERSON | CLERK     | 7902 | 17-DEC-06 00:00:00 | 1300.00 |         |     20
(15 rows)

Delete a synonym

To delete a synonym, use the DROP SYNONYM statement. The statement has the following syntax:

DROP [PUBLIC] SYNONYM [schema.]syn_name
Table 2. Parameters
ParameterDescription
syn_nameThe name of the synonym. A synonym name must be unique within a schema.
schemaThe name of the schema where the synonym is located.

Similar to any other object that can be schema qualified, you may have two synonyms with the same name in your search path. To clarify the name of the synonym that you want to drop, you must include a schema name. Unless a synonym is schema qualified in the DROP SYNONYM statement, a PolarDB for PostgreSQL(Compatible with Oracle) deletes the first instance of the synonym found in your search path.

You can include the PUBLIC clause to drop a synonym that is located in the public schema. Compatible with Oracle databases, the DROP PUBLIC SYNONYM statement drops a synonym that is located in the public schema by using the following syntax:

DROP PUBLIC SYNONYM syn_name;

The following example shows how the personnel synonym is dropped:

DROP SYNONYM personnel;