PostgreSQL Synonyms

Introduction to PostgreSQL Synonyms

PostgreSQL provides synonyms functionality to the user. A synonym is an identifier and it is used to reference other database objects we can also call alternate names of objects. A synonym is useful in a fully qualified schema name and referenced by PostgreSQL statement. It is not necessary a referenced object is must available at the time we create synonyms. A synonym is useful for non-existing schema or objects. When we delete a referenced object or schema that means your synonyms are not valid. If we have two synonyms with the same name that means it is unqualified so at that time the server uses a search path.

Syntax:

create [or replace]     [public]           synonyms        [sche.]synonyms_name
for       obj_sche.obj_name     [#database_link_name];

Explanation:

 1. synonyms_name:

It is used as the name of synonyms. A synonym must be unique within the database or schema.

2. sche:

Sche specified schema name if we don’t specify the schema name then it is automatically created in the first existing schema in the search path.

3. obj_name:

Obj_name means it specifies object name.

4. obj_sche:

Obj_sche specifies the object schema name of schema and its reference to object. 

5. database_link_name:

It is a defined database link and with the help of this link, we can access objects.

How Synonyms works in PostgreSQL?

 We must install PostgreSQL in your system. We required basic knowledge about PostgreSQL. We must require a database table to perform synonyms. We need basic knowledge about the synonyms syntax that means how it is used. We can perform different operations on database tables with the help of psql and pgAdmin. The advanced server supports views, tables, functions, procedures, sequences, types, and objects, etc.

Let’s see how we can implement synonyms by using the following example as follows.

Examples

First, we create table college by using the following statement as follows.

Example #1

create table college (emp_id serial PRIMARY KEY, emp_name varchar(30), emp_dept varchar[],emp_city varchar[],emp_salary text[]);

Explanation

With the help of the above statement, we created a college table with different attributes such as emp_id, emp_name, emp_dept, emp_city, and emp_salary. Illustrate the end result of the above declaration by using the following snapshot.

Example #2

Now we implement synonyms as follows.

A synonym is not to be supported in the PostgreSQL database because PostgreSQL differentiates between schema and user clearly. But oracle we need to use synonyms because of this reason. When we use syntax to create synonyms in PostgreSQL at that time it shows syntax errors. Let’s see an example of synonyms as follows.

create or replace public synonym test for public.college;

Explanation

In the above example, we try to implement synonyms in PostgreSQL but it shows error messages because PostgreSQL does not support synonyms. Illustrate the end result of the above declaration by using the following snapshot.

Example #3 – Search Path

Sometimes we create synonyms by using database links or paths. At that time we use this function as follows.

show search_path;

Explanation

In the above statement, we show the search path command to see the existing schema.  Illustrate the end result of the above declaration by using the following snapshot.

Example #4 – DROP Synonyms

On the other hand, we can DROP synonyms. So let’s see how we can DROP synonyms in PostgreSQL. Suppose users need to delete synonyms at that time and users use the following syntax as follows.

 Syntax:

Drop    public  synonym        synonyms name;

Explanation

In the above syntax, we use the drop synonym command to delete synonyms. When we want to delete synonyms but two synonyms have the same name at that time we check whether the schema is qualified or not.

Example #5 – Synonym Dictionary

PostgreSQL provides a synonyms dictionary function to the user and it is used to replace words with synonyms. Synonyms dictionary is used to overcome the similar word problem. For example, suppose our schema has two similar words like Paris and Pari. So with the help of a synonyms dictionary, we solve that problem.

Example #6 – Conversion of Oracle synonyms to PostgreSQL

PostgreSQL does not support synonyms. But some users or customers need to migrate to PostgreSQL from Oracle. At that time we had a schema conversion tool. With the help of AWS tools, we can convert Oracle synonyms to PostgreSQL synonyms.

Example #7 – Synonyms package conversion of Oracle to PostgreSQL

AWS is not able to convert the oracle synonyms package to PostgreSQL. So let’s see how we can convert as follows.

CREATE OR REPLACE PACKAGE ORA_PACKAGE AS
TYPE oracle_type IS REC (
name VARCHAR2(512),
amount NUMBER);
TYPE r_ora_type IS TABLE OF oracle_type;
v_pi CONSTANT NUMBER(8) :=2.24;
mini_balance CONSTANT REAL := 1.00;
max_balance CONSTANT REAL := 9.00;
CURSOR cur IS SELECT 'Monday' AS "day" FROM dual;
END ORA_PACKAGE;

So we need to write a script to convert this above package into PostgreSQL as follows.

Script for PostgreSQL Constant

CREATE OR REPLACE FUNCTION
schema_name."s_ora_package$schema_name$ora_package$max_balance$c"()
RETURNS DOUBLE PRECISION
LANGUAGE
AS $function$
BEGIN
RETURN 9.00;
END;
$function$
;

Explanation

In the above example, we convert the Oracle synonyms package in PostgreSQL. The above example is specific for PostgreSQL constant, in the above script we create a function as shown in scripts.

Uses

1. PostgreSQL synonyms are used to reference another database object, table, functions, etc.
2. The synonyms we also use to hide our original content from users mean it is used for security purposes.
3. The synonym is used as an alias or alternate name for a view, table, sequence, function, procedure, etc.
4. It also provides backward compatibility for existing objects or schema when you rename them.

Conclusion

We hope from this article you have understood the PostgreSQL synonyms. Actually, PostgreSQL does not support PostgreSQL but it supports Oracle. From the above article, we have learned the basic syntax of synonyms. We have also learned how we can implement them in PostgreSQL with different examples of each operation. We also learned how we can convert Oracle synonyms to PostgreSQL synonyms by using a schema conversion tool. From this article, we have learned how we can handle synonyms in PostgreSQL.

Recommended Articles

This is a guide to PostgreSQL Synonyms. Here we discuss the definition, syntax, examples, uses with code implementation, and How Synonyms works in PostgreSQL? You may also have a look at the following articles to learn more –

1. PostgreSQL Clustered Index
2. PostgreSQL group_concat
3. PostgreSQL Limit Offset
4. PostgreSQL Float