Vertica Blog

Vertica Blog

Quick Tips

Vertica Quick Tip: Sampling Data from the Entire Table

Soniya Shah, Information Developer
February 8, 2018

This blog post was authored by Jim Knicely. A development or quality assurance team often times requests access to a sub-set of production data. One way to do that would be to make use of the LIMIT clause. The problem with that method is the team won’t get a very good sampling of the data....

Vertica Quick Tip: Superfast Table Copy

Soniya Shah, Information Developer
February 7, 2018

This blog post was authored by Jim Knicely. Very often we need to make a copy of a very large table in order to do some development or quality assurance type of duties. Typically we’ll use a CREATE TABLE AS SELECT (CTAS) statement to make that copy. But that took a very long time (~...

Vertica Quick Tip: Add a Time Zone

Soniya Shah, Information Developer
February 6, 2018

This blog post was authored by Jim Knicely. Vertica recognizes many time zones. However, there might come a time (zone) when you will need to reference one that is not available by default. Luckily it’s relatively easy to add a time zone to Vertica. But I would like to use the “Hawaii-Aleutian Daylight Time (HDT)”...

Vertica Quick Tip: Default Size of the NUMBER Data Type

Soniya Shah, Information Developer
February 5, 2018

This blog post was authored by Jim Knicely. When creating a table where you do not define a precision for a NUMBER column data type, Vertica will use a default precision of 38 digits. Often this is larger than necessary. By specifying NUMBER(37) you will potentially get better query performance and save on storage. Why?...

Vertica Quick Tip: Dynamically Split Up a String

Soniya Shah, Information Developer
February 1, 2018

This blog post was authored by Jim Knicely. One of my favorite functions in Vertica is named SPLIT_PART. It splits up a string into parts by a given delimiter. But what if I don’t know how many parts there are in my text? As the following example shows, I can use the ROW NUMBER analytic...
Programmer

Vertica Quick Tip: Proper Ordering of IP Addresses

Soniya Shah, Information Developer
January 31, 2018

This blog post was authored by Jim Knicely. Often times we store IP addresses in a VARCHAR column in a Vertica table. When querying the data and sorting by the IP address, we see that IP addresses are sorted by its VARCHAR value instead of its numeric value. Fortunately Vertica has the INET_ATON function which...

Vertica Quick Tip: The <=> operator

Soniya Shah, Information Developer
January 30, 2018

This blog post was authored by Jim Knicely. The operator performs an equality comparison like the = operator, but it returns true, instead of NULL, if both operands are NULL, and false, instead of NULL, if one operand is NULL. Have fun!

Vertica Quick Tip: A Truly Unique Constraint

Soniya Shah, Information Developer
January 29, 2018

This blog post was authored by Jim Knicely. According to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a UNIQUE constraint should disallow duplicate non-NULL values, but allow multiple NULL values. A Unique Constraint in Vertica does just that! But what if I do not want to allow more than one NULL value? Well, there’s a...

Vertica Quick Tip: Lightning Fast Text Search

Soniya Shah, Information Developer
January 25, 2018

This blog post was authored by Jim Knicely. Searching the contents of a sizeable CHAR, VARCHAR, LONG VARCHAR, VARBINARY, or LONG VARBINARY field within a table to locate a specific keyword can be quite time consuming. Especially when dealing in Big Data. Fortunately, Vertica includes a text indexing feature which allows you to query that...
Database Server Room

Vertica Quick Tip: Generating a Random String

Soniya Shah, Information Developer
January 24, 2018

This blog post was authored by Jim Knicely. We saw in a previous Vertica Quick Tip that we can create a SQL function that generates random dates. How about one that generates random strings? Have fun!

Vertica Quick Tip: Which Rows Will Commit?

Soniya Shah, Information Developer
January 23, 2018

This blog post was authored by Jim Knicely. Did you ever update a bunch of rows in a table, then forget which ones you changed? Fearing you might have updated an incorrect record, you might have to roll back and start again. Or, in Vertica you can first check which records have been modified prior...

Vertica Quick Tip: Date Arithmetic with Intervals

Soniya Shah, Information Developer
January 22, 2018

This blog post was authored by Jim Knicely. In the last Vertica Quick Tip we saw how easy date arithmetic can be. Well, it can be even easier with Intervals! What is today’s, yesterday’s and tomorrow’s date? Again, you’re not limited to whole days! You can also easily add and subtract partial days (i.e. hours,...