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;

vertica_ps_test=> SET search_path TO dim_tables, public;

vertica_ps_test=> SELECT * FROM date_dim;
(1 row)

Have fun!

Sign In or Register to comment.