Displaying the Current Schema

Posted August 8, 2018 by James Knicely, Vertica Field Chief Technologist

High angle view of Beijing Guomao.

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!