If you’ve worked with the Oracle database you know that a synonym is an alias for a schema object such as the tables, views, and other database objects.
Neither Postgres nor YugabyteDB support synonyms, however, we can simulate them with a SEARCH_PATH – a list of schema names searched in order when you refer to an object (i.e. a table) without qualifying the schema name.
Note: We learned about the SEARCH_PATH in this tip: SET SCHEMA SEARCH PATH
Example:
Let’s create several database objects.
yugabyte=# CREATE SCHEMA s1;
CREATE SCHEMA
yugabyte=# CREATE SCHEMA s2;
CREATE SCHEMA
yugabyte=# CREATE TABLE s1.t(c VARCHAR);
CREATE TABLE
yugabyte=# CREATE TABLE s2.t(c VARCHAR);
CREATE TABLE
yugabyte=# INSERT INTO s1.t VALUES ('Schema s1');
INSERT 0 1
yugabyte=# INSERT INTO s2.t VALUES ('Schema s2');
INSERT 0 1
Now let’s simulate synonyms by dynamically changing the schema search path via the SEARCH_PATH setting:
yugabyte=# SET search_path = s1, public;
SET
yugabyte=# SELECT * FROM t;
c
-----------
Schema s1
(1 row)
yugabyte=# SET search_path = s2, public;
SET
yugabyte=# SELECT * FROM t;
c
-----------
Schema s2
(1 row)
The SEARCH_PATH can be set at the user level. This is a great feature if you have set up application users and want them to reference objects in a specific schema when a schema name is not specified in a query.
yugabyte=# CREATE USER my_app_user WITH LOGIN;
CREATE ROLE
yugabyte=# ALTER USER my_app_user SET SEARCH_PATH = s2;
ALTER ROLE
yugabyte=# GRANT USAGE ON SCHEMA s2 TO my_app_user;
GRANT
yugabyte=# GRANT SELECT ON s2.t TO my_app_user;
GRANT
yugabyte=# \! ysqlsh -h yugabytedb.tech -U my_app_user -c "SELECT * FROM t;"
c
-----------
Schema s2
(1 row)
Have Fun!
