Magic of “\d” in Vertica

A quick neat way to list down important and oft-needed information like names of databases, schemas, users, tables, projections etc. We can also use patterns with the ‘\d’ to narrow down the results. Let’s see it in action:

Connect with Vertica vsql:

vsql  -U dbadmin -w vtest -h 0.0.0.0 -p 5433 -d vtest
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

vtest=>
vtest=> \dn
List of schemas
Name     |  Owner  | Comment
————–+———+———
v_internal   | dbadmin |
v_catalog    | dbadmin |
v_monitor    | dbadmin |
public       | dbadmin |
TxtIndex     | dbadmin |
store        | dbadmin |
online_sales | dbadmin |
mytest       | mytest  |
(8 rows)

vtest=> \dn mytest
List of schemas
Name  | Owner  | Comment
——–+——–+———
mytest | mytest |
(1 row)

vtest=> \dn my*
List of schemas
Name  | Owner  | Comment
——–+——–+———
mytest | mytest |
(1 row)

vtest=> \dn v
List of schemas
Name | Owner | Comment
——+——-+———
(0 rows)

vtest=> \dn *v*
List of schemas
Name    |  Owner  | Comment
————+———+———
v_internal | dbadmin |
v_catalog  | dbadmin |
v_monitor  | dbadmin |
(3 rows)

Likewise you can list down other information like:

vtest=> \dj
List of projections
Schema    |            Name             |  Owner  |       Node       | Comment
————–+—————————–+———+——————+———
mytest       | ptest                       | mytest  | v_vtest_node0002 |
mytest       | testtab_super               | mytest  |                  |

To list down views:

vtest=> \dv
No relations found.

If you connect with the mytest user and run:

vtest=> \dt
List of tables
Schema |  Name   | Kind  | Owner  | Comment
——–+———+——-+——–+———
mytest | testtab | table | mytest |
(1 row)

Following are more ‘\d’ options from help:

Informational
\d [PATTERN]   describe tables (list tables if no argument is supplied)
PATTERN may include system schema name, e.g. v_catalog.*
\df [PATTERN]  list functions
\dj [PATTERN]  list projections
\dn [PATTERN]  list schemas
\dp [PATTERN]  list table access privileges
\ds [PATTERN]  list sequences
\dS [PATTERN]  list system tables. PATTERN may include system schema name
such as v_catalog, v_monitor, or v_internal.
Example: v_catalog.a*
\dt [PATTERN]  list tables
\dtv [PATTERN] list tables and views
\dT [PATTERN]  list data types
\du [PATTERN]  list users
\dv [PATTERN]  list views

Discover more about our expertise in Big Data.