Vertica Blog

Vertica Blog

Quick Tips

Fun April Fool’s Day Trick Using Directed Queries

Directed Queries in Vertica encapsulate information that the optimizer can use to create a query plan. Directed queries can serve the following goals: Preserve current query plans before a scheduled upgrade. In most instances, queries perform more efficiently after a Vertica upgrade. In the few cases where this is not so, you can use directed...

Properly Identifying Identifiers

Vertica Identifiers (names) of objects such as schemas, tables, projections, column names, and so on, can be up to 128 bytes in length. Unquoted SQL identifiers must begin with one of the following: Non-Unicode letters: A–Z or a-z Underscore (_) Subsequent characters in an identifier can be any combination of the following: Non-Unicode letters: A–Z...

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!

Find Out When Vertica Was Last Updated

You can check the data collector table DC_UPGRADES to see when you upgraded Vertica by version! Example: 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...

Using sed to Handle Empty String NULL Values Enclosed in Control Characters

You can load data files into a Vertica table using the COPY command. Your data can be delimited and separated by control characters. Unfortunately if you also enclose NULL values as an empty string, you might run into a data cast issue. Example: One work around is to clean up the source file using the...

Using COPY FILLER to Handle Empty String NULL Values Enclosed in Control Characters

You can load data files into a Vertica table using the COPY command. Your data can be delimited and separated by control characters. Unfortunately if you also enclose NULL values as an empty string, you might run into a data cast issue. Example: One work around is to use a COPY FILLER. Helpful Links: Have...
Three 3D arrows, different colors pointing in different directions

Monitor/Clean Up the CopyErrorLogs Directory

The COPY statement automatically saves a copy of each rejected row in a rejected-data file. COPY also saves a corresponding explanation of what caused the rejection in an exceptions file. By default, Vertica saves both files in a database catalog subdirectory called CopyErrorLogs. After you’ve reviewed and resolved any issues with the load, it’s a...

Tracking the Current Transaction Start Date and Time

The built-in Vertica function TRANSACTION_TIMESTAMP returns a value of type TIME WITH TIMEZONE, which represents the start of the current transaction. It’s very useful for keeping track of when the transaction started for a group of table inserts. Example: dbadmin=> INSERT INTO test SELECT 1, SYSDATE, TRANSACTION_TIMESTAMP(); OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO...

Simple ASCII Charting Using SQL

It is very easy to create a simple ASCII chart in Vertica using a SQL analytic function! Example: It’s a lot easier to visualize the differences in row counts when viewing them as a chart column! Helpful Links: