Quick Tip: Combine SQL and vsql Meta-commands on the vsql Command Line

Posted June 10, 2021 by James, Vertica Field Chief Technologist

Vintage businessman concept wearing futuristic helmet at office

When using vsql, the -c (–command) flag lets you pass a single statement to vsql as a string and then exits.

Examples:

[dbadmin@localhost ~]$ vsql -c "SELECT * FROM dual;";
 dummy
-------
 X
(1 row)

[dbadmin@localhost ~]$ vsql -c "\dt seed";
              List of tables
 Schema | Name | Kind  |  Owner  | Comment
--------+------+-------+---------+---------
 public | seed | table | dbadmin |
(1 row)

While you can’t combine a SQL statement with a vsql meta-command

[dbadmin@localhost ~]$ cat run_me.sql
SELECT 'You bet! Vertica Unify 2021 running July 20–22!' Answer;

[dbadmin@localhost ~]$ vsql -c "SELECT 'Are there any can''t miss upcoming summer events?' Question; \i run_me.sql"
ERROR 4856:  Syntax error at or near "\" at character 70
LINE 1: ...ny can''t miss upcoming summer events?' Question; \i run_me....
                                                             ^

…You can use echo to combine and pipe SQL statements and vsql meta-functions to vsql!

[dbadmin@localhost ~]$ echo "SELECT 'Are there any can''t miss upcoming summer events?' Question; \i run_me.sql" | vsql
                     Question
--------------------------------------------------
 Are there any can't miss upcoming summer events?
(1 row)

                     Answer
-------------------------------------------------
 You bet! Vertica Unify 2021 running July 20–22!
(1 row)

Have Fun!