Vertica Blog
Jim Knicely

Jim Knicely

Vertica Field Chief Technologist

I've had the privilege of working with many database technologies in my career. But after being introduced to Vertica in May of 2011 as a client, I was hooked on the new technology after witnessing a query run in milliseconds that had previously ran for hours on the legacy database we had in place. It was then that I knew I wanted to eventually join the Vertica team, and 4 years later I did! I am currently a Vertica evangelist and am ready to help you get on board! Please feel free to reach out to me with any questions you have about Vertica and make sure to follow my Vertica Quick Tips!

Connect With Jim on

Create a User Defined SQL Function to Calculate Fibonacci Numbers: Quick Tip

The Fibonacci Sequence is the series of numbers (i.e. 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, ...) where the next number in the sequence is found by taking the sum of the previous two numbers. Calculating these numbers in SQL would be a bit complicated if it were not for the “Golden...

Insert Spaces Into a Character String: Quick Tip

In many SQL relational database you will have to use the RPAD function to insert spaces into a character string. That also works in Vertica. However, for a more robust solution, Vertica provides the built-in function SPACE which returns the specified number of blank spaces. Helpful Links: Have fun!

Find the Version of Vertica that Created a Database: Quick Tip

You can run the VERSION() function as one method of displaying the current version of Vertica. But what if you want to know the version of Vertica running when you created the current database? For that info you can query the VS_GLOBAL_SETTINGS table. Helpful Link: Have fun!

Rejected Data Table Row Number: Quick Tip

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause saves rejected data into a table. The rejected data table includes an informative column called ROW_NUMBER where its value indicates the rejected row number from the input file. Be aware that when a COPY encounters an empty line while loading...

Reload Data from a Rejected Data Table: Quick Tip

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause, will save rejected data into a table. If you realize there is a modification to the COPY command that will allow those rejected records to load successfully, you can re-run the updated COPY command against the rejected data in the...

Handling Cast Conversion Load Errors: Quick Tip

The nifty cast ::! returns all cast failures as NULL instead of generating an error if a the data type cannot be coerced. This cast feature, combined with the FILLER option of the COPY command, is very useful for loading data when data types aren’t playing nice. Vertica cannot implicitly cast the NULL value to...

Return All Cast Failures as NULL: Quick Tip

When you invoke data type coercion (casting) by an explicit cast and the cast fails, the result returns either an error or NULL. Cast failures commonly occur when you attempt to cast conflicting conversions, such as trying to convert a varchar expression that contains letters to an integer. However, using the nifty cast ::! instead...

Display Canceled Queries: Quick Tip

We can cancel a long running query in vsql by typing CTRL+C. The data collector table DC_CANCELS tracks queries that were stopped in this manner. Now I will run a simple SQL statement and hit CTRL+C after waiting for 2 seconds: Helpful Link: Have fun!

Calculate Request Queue Length: Quick Tip

The RESOURCE_ACQUISITIONS system table retains information about resources (memory, open file handles, threads) acquired by each running request. Each request is uniquely identified by its transaction and statement IDs within a given session. From this system table, you can calculate how long a request was queued in a resource pool before acquiring the resources it...

Concatenate non-NULL Values from a Group: Quick Tip

Vertica 9.1.1-4 introduces an extremely useful aggregate function named LISTAGG, which returns a string with concatenated non-NULL values from a group. Have fun!

Simplify String Literals with Dollar-Quoted String Literals: Quick Tip

The standard syntax for specifying string literals can be difficult to understand. To allow more readable queries in such situations, Vertica SQL provides dollar quoting. Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant single quote syntax. Helpful Link:...

Re-Compute a Table Column’s Default Value Immediately: Quick Tip

Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. That DEFAULT expression can be derived from another column in the same table! When you update the value in a base column, you will need to re-compute the value in your derived column (the...