Vertica Blog

Vertica Blog

VSQL

Accept the EULA When Installing Vertica

After installing Vertica, you have to ACCEPT the EULA the first time you start admintools on any Vertica node. To avoid having to do that, you can accept the EULA during the install using the --accept-eula or –Y option! Example: Helpful Links: Have fun!

Run the Same Query Over and Over Again in vsql

The \g vsql meta-function sends the query in the input buffer to the server. This is great for interactively testing if a previous query run time was a fluke, and you don’t feel like typing in a long SQL statement again. Example: Helpful Link: Have fun!

Display a Query Title in vsql

The \C vsql meta-function allows you to specify a title string to display with query output! Example: Helpful Link: Have fun!

Convert a String to a Date, Including Unconventional Dates

The built-in Vertica function TO_DATE converts a string value to a DATE type. Example: We all know that there are 31 days in March. But are there 32 or 33 days in March? No. Vertica simply extends the date returned into the next month! Also note there is no 13th month! Helpful Link: Have fun!

Simulate NULLS FIRST and NULLS LAST in the ORDER BY Clause

When your query contains the ORDER BY clause to sort the result set, alphanumeric NULL data will sort to the bottom if the sort is in ascending order (ASC) and to the top if the sort is in descending order (DESC), while integer NULL data does the opposite. Example: I want the NULL values in...

Changing the Field Separator in VSQL: Quick Tip

vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. It’s very common to want to export data in CSV (Comma-Separated Values) format. To do that you can change the default | (vertical bar) field separator to a comma via the fieldsep option of the pset meta-command. Wait...

Displaying the Current Schema

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). dbadmin=> SHOW search_path;...
Programmer

Vertica Quick Tip: Empty String Vs. NULL

An empty string ('') is treated as a NULL value in Oracle, while in Vertica an empty string is not treated as a NULL value. So when using Vertica, if you want to indicate that a column value is unknown, be sure to use NULL and not an empty string! In Oracle: Are they the...