Synonyms: Quick Tip

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!