Create and remove synonyms for database objects - PolarDB

Synonyms are alternative names for database objects. A synonym allows you to reference an object without using the fully qualified name (schema name and object name), simplifying references to database objects in SQL statements.

Important

Synonyms don't replace privileges. Users must have the required privileges on the underlying object before they can use a synonym to access it.

In PolarDB for PostgreSQL (Compatible with Oracle), you can create synonyms for the following object types:

Tables
Views
Materialized views
Sequences
Procedures
Functions
Custom types
Packages
Objects accessible via a database link

How synonyms work

A synonym references an underlying database object. The referenced object doesn't need to exist when you create the synonym—PolarDB for PostgreSQL (Compatible with Oracle) allows synonyms to reference non-existent objects or schemas.

When an SQL statement uses a synonym, PolarDB for PostgreSQL (Compatible with Oracle) checks the current user's privileges on the underlying object, not on the synonym itself. If the user lacks the required privileges, the statement fails.

Name resolution: PolarDB for PostgreSQL (Compatible with Oracle) resolves unqualified synonym names using the search path. If two synonyms share the same name, an unqualified reference resolves to the first match found in the search path. If public is in your search path, you can reference a synonym in the public schema without qualifying the name.

Synonym lifecycle: A synonym becomes invalid after you drop the referenced object or schema. Dropping the referenced object doesn't automatically remove the synonym—you must drop it explicitly.

Where synonyms can be used: You can use synonyms in any DDL or DML SQL statement.

Create a synonym

Syntax

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

Parameter	Description
`syn_name`	Name of the synonym. Must be unique within a schema.
`schema`	Schema in which the synonym is created. If omitted, the synonym is created in the first existing schema in your search path.
`object_name`	Name of the object for which the synonym is created.
`object_schema`	Schema in which the object resides.
`dblink_name`	Name of the database link. Required if the object is accessed via a database link.

Clauses:

OR REPLACE: Replaces an existing synonym with the new definition.
PUBLIC: Creates the synonym in the public schema. Equivalent to creating the synonym with public.syn_name.

Create a public synonym

The CREATE PUBLIC SYNONYM statement, compatible with Oracle databases, creates a synonym in the public schema:

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

Shorthand syntax:

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

Example

Create a synonym named personnel that references the polardb.emp table:

CREATE SYNONYM personnel FOR polardb.emp;

Because no schema is specified, the synonym is created in the first existing schema in your search path. To check your search path:

SHOW SEARCH_PATH;

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

In this example, if schema development doesn't exist, the synonym is created in schema accounting.

Once created, use the personnel synonym in any DML or DDL statement:

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)

Drop a synonym

Syntax

DROP [PUBLIC] SYNONYM [schema.]syn_name

Parameter	Description
`syn_name`	Name of the synonym to drop.
`schema`	Schema in which the synonym resides.

Without a schema qualifier, PolarDB for PostgreSQL (Compatible with Oracle) drops the first instance of the synonym found in the search path. To drop a specific synonym when two synonyms share the same name, include the schema name.

To drop a synonym from the public schema, use the PUBLIC clause. The DROP PUBLIC SYNONYM statement, compatible with Oracle databases, removes a synonym from the public schema:

DROP PUBLIC SYNONYM syn_name;

Example

Drop the personnel synonym:

DROP SYNONYM personnel;