Vertica Blog

Vertica Blog

Quick Tips

Vertica Quick Tip: Getting the Root of a Number

Soniya Shah, Information Developer
April 18, 2018

This blog post was authored by Jim Knicely. You can find the square root of a number with the |/ operator and the cube root of a number with the ||/ operator. Have Fun!

Vertica Quick Tip: Using a Preferred Editor in VSQL

Soniya Shah, Information Developer
April 17, 2018

This blog post was authored by Jim Knicely. The vsql \e and \edit meta-commands let you edit the query buffer (or specified file) with an external editor. The default editor on Linux is vi and notepad.exe on Windows systems. Because vsql searches the environment variables VSQL_EDITOR, EDITOR, and VISUAL (in that order) for an editor...

Vertica Quick Tip: Extract Time from a Date

Soniya Shah, Information Developer
April 16, 2018

This blog post was authored by Jim Knicely. There are multiple ways to extract just the time from date in Vertica. Which method you choose depends on your preferred result data type. dbadmin=> SELECT * FROM public.test; sysdate | just_time_as_time | just_time_as_varchar ----------------------------+-------------------+---------------------- 2018-04-16 10:17:16.906347 | 10:17:16.906347 | 10:17:16:906347 (1 row) dbadmin=> \d public.test List...
Three 3D arrows, different colors pointing in different directions

Vertica Quick Tip: Add Days to a Date, excluding SAT and SUN

Soniya Shah, Information Developer
April 12, 2018

This blog post was authored by Jim Knicely. Say I want to add 12 days to today’s date April, 12, 2018. That’s easy using date arithmetic. But what if I want to only add 12 “business” days and exclude the “weekend” days? That’s not as easy, but not too difficult thanks to Vertica’s TIMESERIES clause!...

Vertica Quick Tip: Converting Intervals to Numeric

Soniya Shah, Information Developer
April 11, 2018

This blog post was authored by Jim Knicely. Intervals measure the difference between two points in time. Converting an interval to a numeric is easy in Vertica because we can perform operations (i.e. division) on them! Examples: dbadmin=> SELECT INTERVAL '36 HOURS' / INTERVAL '1 DAY' days; days ------ 1.5 (1 row) dbadmin=> SELECT INTERVAL...
Modern Database Analytics

Vertica Quick Tip: Extract Just Numbers from a String of Characters

Soniya Shah, Information Developer
April 10, 2018

This blog post was authored by Jim Knicely. The easiest way to extract just the numbers from a string of characters is to simply remove any character that isn’t a number! Have Fun!

Vertica Quick Tip: Simulating DML Operations on External Tables

Soniya Shah, Information Developer
April 4, 2018

This blog post was authored by Jim Knicely. An external table lets us query a text file that sits outside of the database as if it were a table internal to the database. Unfortunately, there is a limitation on external tables in that normal DML statements (INSERT, UPDATE and DELETE) cannot be used on them....

Vertica Quick Tip: How old am I?

Soniya Shah, Information Developer
April 3, 2018

This blog post was authored by Jim Knicely. The AGE_IN_YEARS function returns the difference in years between two dates, expressed as an integer. This function is very useful when I forget how old I am or how old my wife is being that today is her birthday. Have Fun!

Vertica Quick Tip: Monitoring CPU Usage

Soniya Shah, Information Developer
April 3, 2018

This blog post was authored by Jim Knicely. The V_MONITOR.CPU_USAGE system table records the CPU usage history by node. I can easily find the top 5 highest average CPU usage on my cluster in the previous 24 hours: Have Fun!

Vertica Quick Tip: Projection Create Types

Soniya Shah, Information Developer
April 2, 2018

This blog post was authored by Jim Knicely. The DDL produced by the EXPORT_OBJECTS function typically contains a create type for a projection. In the above example the create type is L for “Lazy”, meaning that I inserted data into the table when no projections existed yet. Here is a list of the projection create...

Vertica Quick Tip: Summary of Cluster State

Soniya Shah, Information Developer
March 29, 2018

This blog post was authored by Jim Knicely. The GET_CLUSTER_STATE_SUMMARY function can be used to quickly view the current status of your Vertica cluster. All nodes are up: One node is down: dbadmin=> SELECT get_cluster_state_summary(); get_cluster_state_summary --------------------------------------------------------------------------------------------------------------------------- Cluster State: test2 UNKNOWN: 1 of 4 (v_test2_node0004) UP: 3 of 4 (v_test2_node0001, v_test2_node0002, v_test2_node0003) (1 row) Have...

Vertica Quick Tip: What’s the Maximum Column Size?

Soniya Shah, Information Developer
March 28, 2018

This blog post was authored by Jim Knicely. There exists a maximum number of characters that can be stored in columns having a data type of VARCHAR or LONG VARCHAR. In case you’ve forgotten those maximum values for the version of Vertica you are using, there are two handy functions that provide this information. To...