Synonyms v17

A synonym is an identifier that you can use to reference another database object in a SQL statement. A synonym is useful in cases where a database object normally requires full qualification by schema name to be properly referenced in a SQL statement. A synonym defined for that object simplifies the reference to a single, unqualified name.

EDB Postgres Advanced Server supports synonyms for:

• Tables
• Views
• Materialized views
• Sequences
• Packages
• Procedures
• Functions
• Types
• Objects that you can access through a database link
• Other synonyms

Neither the referenced schema or referenced object must exist when you create the synonym. A synonym can refer to a nonexistent object or schema. A synonym becomes invalid if you drop the referenced object or schema. You must explicitly drop a synonym to remove it.

As with any other schema object, EDB Postgres Advanced Server uses 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 given name in the search path. If public is in your search path, you can refer to a synonym in that schema without qualifying that name.

When EDB Postgres Advanced Server executes an SQL command, the privileges of the current user are checked against the synonym’s underlying database object. If the user doesn't have the proper permissions for that object, the SQL command fails.

Creating a synonym

Use the CREATE SYNONYM command to create a synonym. The syntax is:

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

Parameters

syn_name

syn_name is the name of the synonym. A synonym name must be unique in a schema.

schema

schema specifies the name of the schema that the synonym resides in. If you don't specify a schema name, the synonym is created in the first existing schema in your search path.

object_name

object_name specifies the name of the object.

object_schema

object_schema specifies the name of the schema that the object resides in.

dblink_name

dblink_name specifies the name of the database link through which you can access a target object.

Include the REPLACE clause to replace an existing synonym definition with a new synonym definition.

Include the PUBLIC clause to create the synonym in the public schema. Compatible with Oracle databases, the CREATE PUBLIC SYNONYM command creates a synonym that resides in the public schema:

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

This is a shorthand way to write:

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

This example creates a synonym named personnel that refers to the enterprisedb.emp table:

CREATE SYNONYM personnel FOR enterprisedb.emp;

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

SHOW SEARCH_PATH;

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

Example

In the example, if a schema named development doesn't exist, the synonym are created in the schema named accounting.

Now you can reference the emp table in the enterprisedb schema in any SQL statement (DDL or DML) by using the synonym personnel:

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)

Deleting a synonym

To delete a synonym, use the command DROP SYNONYM. The syntax is:

DROP [PUBLIC] SYNONYM [<schema>.] <syn_name>

Parameters

syn_name

syn_name is the name of the synonym. A synonym name must be unique in a schema.

schema

schema specifies the name of the schema in which the synonym resides.

Like any other object that can be schema qualified, you can have two synonyms with the same name in your search path. To disambiguate the name of the synonym that you're dropping, include a schema name. Unless a synonym is schema qualified in the DROP SYNONYM command, EDB Postgres Advanced Server deletes the first instance of the synonym it finds in your search path.

You can optionally include the PUBLIC clause to drop a synonym that resides in the public schema. Compatible with Oracle databases, the DROP PUBLIC SYNONYM command drops a synonym that resides in the public schema:

DROP PUBLIC SYNONYM <syn_name>;

Example

The following example drops the synonym personnel:

DROP SYNONYM personnel;