Vertica Blog

Vertica Blog

DBadmin

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: https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/Meta-Commands/MetaCommandReference.htm 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: https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/Meta-Commands/MetaCommandReference.htm Have fun!
Database Server Room

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: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/Glossary/DataCollector.htm 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: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/BulkLoadingData.htm...
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...
Modern Database Analytics

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: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/String/REPEAT.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/MAXAnalytic.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm

Arithmetic Bit-Shifting

A fun way to manipulate bits in Vertica SQL is with the use of arithmetic bit-shifting, which moves the bits in a number either left or right and fills in the new values with 0s. Bit-shifting left is an easy way to multiply by powers of 2, while bit-shifting right divides by powers of 2....

Convert an Integer to a Binary

Although there is not a built-in function in Vertica to convert an integer to a binary, you can use the Vertica TO_HEX and HEX_TO_BINARY functions to create your own! Example: Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/String/HEX_TO_BINARY.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/DataTypes/BinaryDataTypes.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Numeric/INTEGER.htm