Synonyms

[Deleted User]

Jim Knicely authored this post.

A synonym in Oracle is an alternative name for a database object (i.e., table, view, sequence, etc.). Synonyms work great when you want to point a user to a specify schema when a schema is not specified in a query.

Although synonyms are not used in Vertica, you can instead use a variable called SEARCH_PATH that lists in order the schemas where Vertica tries to resolve the full path to the object. By placing a schema earlier in a user’s SERACH_PATH, you can mimic the behavior of a synonym.

Example:

I am logged into Vertica via vsql as the VERTICA_PS_TEST user and want to query a table named DATE_DIM, which is in the DIM_TABLES schema, without specifying the schema name.

vertica_ps_test=> SELECT * FROM date_dim;
ERROR 4566:  Relation "date_dim" does not exist

That didn’t work!

A synonym would definitely help here, but so does the SEARCH_PATH variable in Vertica!

dbadmin=> ALTER USER vertica_ps_test search_path dim_tables, public;
ALTER USER

vertica_ps_test=> SET search_path TO dim_tables, public;
SET

vertica_ps_test=> SELECT * FROM date_dim;
   a_date
------------
2018-07-16
(1 row)

Have fun!