Adding a Column Check Constraint

Vertica does not support check constraints for table columns. There are some pretty darn good reasons for that! Probably the best reason is the fact that we want to load data into Vertica super fast. To do that Vertica ignores all constraints assuming we’ve already scrubbed the data we’re loading. So why introduce another hurdle? […]

Read more

AUTOCOMMIT Variable Overrides COPY Command NO COMMIT Option!

According to the documentation, the NO COMMIT option of the COPY command “Prevents the COPY statement from committing its transaction automatically when it finishes copying data.” However, that’s not the case if the AUTOCOMMIT variable is set to ‘on’… Example: I have a table named JULES that I will load 3 records into using the […]

Read more

Converting Columns to Rows

Sometimes data is entered in columns but what you really want is to rearrange that data into rows instead. There is a method to transpose data in Vertica using SQL, but it is a bit tedious. Say we have the following data in a Vertica database table named TEST: How would we calculate the average […]

Read more

The STRCMP Function

MySQL has an interesting function name STRCMP mimicking the C++ function having the same name. This function compares two values, returning 0 if the strings are the same, -1 if the first argument is smaller than the second, and 1 otherwise. Vertica doesn’t have this function built-in, but it’s very easy to make our own. […]

Read more

Finding the Roots of a Number

Most databases have a built in function we can use to find the square root of a number. But what about the cube root? Vertica provides the SQRT() function to find the square root and the CBRT() function to find the cube root of a number. Vertica, like PostgreSQL, also provides some cool mathematical operators […]

Read more

What is the Data Type of NULL?

Just for fun, let’s try and figure out what is the data type of NULL! NULL is a marker that represents missing, unknown, or inapplicable data. NULL is un-typed in SQL, meaning that it is not designated as a NUMBER, CHAR, or any other specific data type. Is it always true that “Null is un-typed“? […]

Read more

Combining Top and Bottom Limits

The LIMIT clause is used to specify the maximum number of result set rows to return form a query. Example: Say we have a table EMP_AVAIL_VAC_DAYS that tracks the number of vacation days available for employees: As the employee’s manager, I may want to know the top three employees having the most vacation. I can […]

Read more

Finding Unix Time

Unix time, or POSIX time, is a system for describing instances in time. It’s generally defined as the number of seconds that have elapsed since midnight Coordinated Universal Time (UTC), January 1, 1970. Unix time is used widely in Unix-like and many other operating systems and file formats. It is neither a linear representation of […]

Read more

Case Insensitive String Search

There are times while searching column data in tables that we’ll only know a portion of the actual value we desire to find. For these types of queries we can use the LIKE predicate. The LIKE predicate retrieves rows where the string value of a column matches a specified pattern which can contain one or […]

Read more

Multi-Record Insert Statement

The MySQL and Oracle databases have a cool feature in that we can insert more than one record into a table at the same time using a single INSERT statement. Here’s an example using Oracle: And here is an example using MySQL: The Vertica INSERT statement does not have the built in capability to perform […]

Read more