Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.