Vertica-Solutions

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 […]

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 […]

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. […]

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 […]

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“? […]

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 […]

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 […]

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 […]

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 […]