Displaying the Current Schema

Jim Knicely authored this post.

Each user session has a search path of schemas. Vertica uses this search path to find tables and user-defined functions (UDFs) that are unqualified by their schema name.

You can use the CURRENT_SCHEMA function to display the name of the current schema (i.e., the first “valid” schema in the user’s search path).

Example: dbadmin=> SELECT user; current_user -------------- dbadmin (1 row) dbadmin=> SHOW search_path; name | setting -------------+--------------------------------------------------- search_path | "$user", public, v_catalog, v_monitor, v_internal (1 row) dbadmin=> SELECT current_schema; current_schema ---------------- public (1 row) dbadmin=> CREATE SCHEMA dbadmin; CREATE SCHEMA dbadmin=> SELECT current_schema; current_schema ---------------- dbadmin (1 row) dbadmin=> CREATE TABLE my_test (c INT); -- No SCHEMA specified CREATE TABLE dbadmin=> SELECT table_schema FROM tables WHERE table_name = 'my_test'; table_schema -------------- dbadmin (1 row) Have fun!