Vertica Blog

Vertica Blog

DBadmin

Run the Same Query Over and Over Again in vsql

James Knicely, Vertica Field Chief Technologist
March 28, 2019

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

James Knicely, Vertica Field Chief Technologist
March 27, 2019

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!

Find Out When Vertica Was Last Updated

James Knicely, Vertica Field Chief Technologist
March 25, 2019

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

James Knicely, Vertica Field Chief Technologist
March 20, 2019

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

James Knicely, Vertica Field Chief Technologist
March 19, 2019

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

James Knicely, Vertica Field Chief Technologist
March 18, 2019

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

James Knicely, Vertica Field Chief Technologist
March 15, 2019

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

James Knicely, Vertica Field Chief Technologist
March 14, 2019

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

James Knicely, Vertica Field Chief Technologist
March 13, 2019

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

James Knicely, Vertica Field Chief Technologist
March 12, 2019

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

James Knicely, Vertica Field Chief Technologist
March 11, 2019

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

Switch the Sign of a Number

James Knicely, Vertica Field Chief Technologist
March 8, 2019

To switch the sign of a number you can multiply it by -1. Or you can use the Vertica built-in function NUMERIC_UM to do it for you!