Vertica Blog

Vertica Blog

DBadmin

Vertica Quick Tip: Which Column in my Flattened Table caused the “Subquery used as an expression returned more than one row” Error?

Soniya Shah, Information Developer
May 29, 2018

This blog post was authored by Jim Knicely. A flattened table contains columns that are derived from query-expressions that must return only one row and column value, or none. If a column’s query-expression does return more than one row you will get a “Subquery used as an expression returned more than one row” error. If...

Vertica Quick Tip: Monitoring the Ancient History Mark

Soniya Shah, Information Developer
May 23, 2018

This blog post was authored by Jim Knicely. Also known as AHM, the ancient history mark is the oldest epoch whose data is accessible to historical queries. Any data that precedes the AHM is eligible to be purged. If a cluster nodes is down or if a database contains projections that need to be refreshed,...

Vertica Quick Tip: Determining Table Columns that Contain NULL Values

Soniya Shah, Information Developer
May 22, 2018

This blog post was authored by Jim Knicely. A client recently asked if we had a Vertica function that could return a list of columns from a table which contain NULL values. There aren’t any built-in functions for that, but there is always a fast solution available in Vertica! Here’s one! dbadmin=> CREATE OR REPLACE...

Vertica Quick Tip: Getting Better Performance via External Table Row Counts

Soniya Shah, Information Developer
May 21, 2018

This blog post was authored by Jim Knicely. The Vertica optimizer uses its own internal logic to determine whether to join one table to another as an inner or outer input. When joining external tables, you can help out the optimizer immensely by letting it know the exact row counts of those external tables! For...

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: 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...

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!