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.
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)