Vertica Blog

Vertica Blog

Quick Tips

Three 3D arrows, different colors pointing in different directions

Vertica Quick Tip: Keeping DbVisualizer Alive

Soniya Shah, Information Developer
May 17, 2018

This blog post was authored by Jim Knicely. Clients often use DbVisualizer as their preferred GUI tool to query Vertica. Developers and DBAs tend to leave DbVisualizer open on their desktop while they are off doing some other activity, only to return to find out that DbVisualizer has disconnected from Vertica. Most likely the Database...

Vertica Quick Tip: Remove Duplicate Values from a String

Soniya Shah, Information Developer
May 16, 2018

This blog post was authored by Jim Knicely. Ever wonder how to get rid of those pesky duplicate values from a string? One way of doing that is via the REGEXP_REPLACE function. Have Fun!

Vertica Quick Tip: When Modifying a SEARCH_PATH, Don’t Forget to Include PUBLIC

Soniya Shah, Information Developer
May 15, 2018

This blog post was authored by Jim Knicely. Each user session has a search path of schemas. Vertica uses this search path to find tables and user-defined functions (UDFs) that are unqualified by their schema name. You can change a session's search path at any time by calling SET SEARCH_PATH. When you modify a search...

Vertica Quick Tip: Use the Overlay Function to Easily Insert a String Into Another String

Soniya Shah, Information Developer
May 14, 2018

This blog post was authored by Jim Knicely. The OVERLAY function returns a VARCHAR value representing a string having had a substring replaced by another string. Have Fun!

Vertica Quick Tip: Who am I?

Soniya Shah, Information Developer
May 10, 2018

This blog post was authored by Jim Knicely. There are several ways to figure out who is the session user. This comes in handy for logging. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit jim=> select user; current_user -------------- jim (1 row) jim=>...

Vertica Quick Tip: Eon Mode

Soniya Shah, Information Developer
May 9, 2018

This blog post was authored by Jim Knicely. You can now deploy a Vertica cluster with an Eon Mode database instead of an Enterprise mode database. In Enterprise Mode, the original Vertica "share nothing" configuration, each database node stores a portion of the data and performs a portion of the computation. In Eon Mode, computational...

Vertica Quick Tip: Using vsql Almost Anywhere

Soniya Shah, Information Developer
May 8, 2018

This blog post was authored by Jim Knicely. I’m an old school kind of guy so I love our vsql command line interface (CLI) tool. What’s cool is if I am on a remote Linux host, I can copy the vsql binary from the database host and run then run it locally. [dbadmin@s18384357 ~]$ scp...

Vertica Quick Tip: User Defined Functions to the Rescue

Soniya Shah, Information Developer
May 7, 2018

This blog post was authored by Jim Knicely. A friend recently asked me why we don’t have certain functions available in Vertica that are offered in databases like MySQL. I shrugged it off as we have bigger fish to fry. But he kept pushing so I showed him how easy it is to implement a...
Three 3D arrows, different colors pointing in different directions

Vertica Quick Tip: Get Rid of all Those Q’s

Soniya Shah, Information Developer
May 3, 2018

This blog post was authored by Jim Knicely. If you guys are like me I prefer the CLI. So admintools is my friend. However, at many client sites I see admintools is kind of ugly. So to get it looking a little better: Run this in Linux: And add the command to your .bashrc file...

Vertica Quick Tip: Forget the Column Name of a System Table?

Soniya Shah, Information Developer
May 2, 2018

This blog post was authored by Jim Knicely. If you are like me I am constantly forgetting the column names from our system tables. Use this simple trick to get the column names. Have Fun!
Modern Database Analytics

Vertica Quick Tip: Capitalize Only the First Letter of Each Word

Soniya Shah, Information Developer
May 1, 2018

This blog post was authored by Jim Knicely. A few years ago when I was an Oracle DBA a client thought they’d stump me by asking how they could capitalize only the first letter of each word for a given input. I immediately said try the initcap function. She was amazed. Guess what, Vertica has...

Vertica Quick Tip: Check if a Date Range Overlaps another Date Range

Soniya Shah, Information Developer
April 19, 2018

This blog post was authored by Jim Knicely. The OVERLAPS Vertica built-in function evaluates two time periods and returns true when they overlap, false otherwise. dbadmin=> SELECT (DATE '2018-04-18', DATE '2018-04-21') OVERLAPS (DATE '2018-04-22', DATE '2018-04-25'); overlaps ---------- f (1 row) Have Fun!