Using vsql

vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. It also provides a number of meta-commands and various shell-like features that facilitate writing scripts and automating a variety of tasks.

If you are using the vsql client installed on the server, then you can connect from the:

• Administration Tools

• Linux command line

You can also install the vsql client for other supported platforms.

General notes

• SQL statements can be spread over several lines for clarity.

• vsql can handle input and output in UTF-8 encoding. The terminal emulator running vsql must be set up to display the UTF-8 characters correctly. The following example shows the settings in PuTTy:

  

  See also Best Practices for Working with Locales.

• Cancel SQL statements by typing Ctrl+C.

• Traverse command history by typing Ctrl+R.

• When you disconnect a user session, any transactions in progress are automatically rolled back.

• To view wide result sets, use the Linux less utility to truncate long lines.

  1. Before connecting to the database, specify that you want to use less for query output:

     $ export PAGER=less
     

  2. Connect to the database.

  3. Query a wide table:

     => select * from wide_table;
     

  4. At the less prompt, type:

     -S
     

  If a shell running vsql fails (crashes or freezes), the vsql processes continue to run even if you stop the database. In that case, log in as root on the machine on which the shell was running and manually terminate the vsql process. For example: $ ps -ef | grep vertica ... fred 2401 1 0 06:02 pts/1 00:00:00 /opt/vertica/bin/vsql -p 5433 -h test01_site01 quick_start_single ... $ kill -9 2401

Enabling autocommit

By default, you must COMMIT to save changes made in a transaction. To enable automatic commits, see SET SESSION AUTOCOMMIT.