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

Using VSQL as a Calculator: Quick Tip

A calculator is something used for making mathematical calculations, in particular a small electronic device with a keyboard and a visual display. VSQL mostly fits that description! And for fun, you can also astound your friends using this old calculator trick! 1. Have a buddy pick a number between 1 and 9. 2. Now have...

Extracting Characters from the Left and Right Side of a LONG VARCHAR: Quick Tip

The LEFT SQL function returns the specified characters from the left side of a string, while the RIGHT SQL function returns the specified characters from the right side of a string. Unfortunately, the LEFT and RIGHT SQL functions won’t work on table columns having the LONG VARCHAR data type where the data size can be...

Determining the Current K-Safety: Quick Tip

K-safety sets the fault tolerance in your Vertica database cluster. The value K represents the number of times the data in the database cluster is replicated. These replicas allow other nodes to take over query processing for any failed nodes. You can view the current K-Safety of a Vertica database in several ways. Helpful Links:...

Specifying CASCADE When Dropping a Table: Quick Tip

The DROP TABLE command removes a table and its projections. A projection with a create type of “DELAYED CREATION” is an auto-projection. Auto-projections are superprojections that Vertica automatically generates for tables. If you create a projection manually or via the Database Designer (non-auto projections), you will need to drop the table specifying the CASCADE option....

Ordering the List of Values Returned from LISTAGG

The LISTAGG function transforms non-null values from a group of rows into a list of values that are delimited by a configurable separator. LISTAGG can be used to de-normalize rows into a string of comma-separated values or other human-readable formats. What if I want to order the list of values that LISTAGG returns? Unfortunately, there...

Ignore NULL Values in Non-Correlated Subqueries: Quick Tip

A subquery is a SELECT statement embedded within another SELECT statement. The embedded subquery is often referenced as the query's inner statement, while the containing query is typically referenced as the query's statement, or outer query block. A subquery returns data that the outer query uses as a condition to determine what data to retrieve....

Drop More than One Table at a Time: Quick Tip

The DROP TABLE command removes a table and its projections. You can drop more than one table at a time by specifying a comma delimited set of tables! That was easy. How about dropping more than one table? At first you might be tempted to run a DROP TABLES command to drop multiple tables, but...

Allowing Users to Query a View Owned by Another User: Quick Tip

As we learned in yesterday’s Vertica Quick Tip , the owner of a View must have direct access to the underlying objects referenced by the view! But what if another user wants to read a view owned by another user? In this case, not only must the View owner have read access to the View’s...

A View Owner Needs Access to the Underlying Objects: Quick Tip

You can use the ALTER VIEW … OWNER TO command to change a Vertica database view’s ownership. It’s important to know that a View’s owner must also have access to the underlying objects referenced by the view! Exanple: Great! The user, USER1, can read from the view as expected. But what happens when I change...

Preserving Objects Owned by a Dropped User: Quick Tip

When you drop a user with the CASCADE option, all objects owned by that user are lost forever! If you’d prefer to save those objects, you first set the GlobalHeirUserName security parameter to a user who will inherit objects after their owners are dropped. This setting ensures preservation of data otherwise lost. Helpful Link: Have...

List Table Rows by ROS Container: Quick Tip

A ROS (Read Optimized Store) container is a set of rows stored in a particular group of files. ROS containers are created by operations like Moveout or COPY DIRECT. You can query the STORAGE_CONTAINERS system table to see ROS containers. You can use the LEAD_STORAGE_OID function to list the rows from a table that are...
Three 3D arrows, different colors pointing in different directions

Tracking Save Points: Quick Tip

The SAVEPOINT SQL command creates a special mark, called a savepoint, inside a transaction. A savepoint allows all commands that are executed after it was established to be rolled back, restoring the transaction to the state it was in at the point in which the savepoint was established. Savepoints can be nested so it can...