Vertica Blog

Vertica Blog

DBadmin

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

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!

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

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

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!

Vertica Quick Tip: Getting the Root of a Number

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: Extract Time from a Date

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

Vertica Quick Tip: Converting Intervals to Numeric

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

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

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

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. dbadmin=> SELECT * FROM public.mood; pk | mood ----+------------- 1 | Happy 2 | Sad 3 | Indifferent 4 | Surprised (4...

Vertica Quick Tip: How old am I?

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

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!