Vertica Blog
Sarah Lemaire smiling

Sarah Lemaire

Manager, Vertica Documentation

Connect With Sarah on

Database Server Room

Generate Random Integers, Including Negative Numbers

Sarah Lemaire, Manager, Vertica Documentation
August 6, 2018

Jim Knicely authored this tip. The RANDOMINT(n) function returns one of the n integers from 0 through n – 1. Those are all positive integers. What if I want to include negative integers? That’s easy with a simple multiplication. dbadmin=> SELECT DECODE(randomint(2), 1, 1, -1) * randomint(11) "Random INT from -10 to 10"; Random INT...

Summarize a Table’s Projections: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
August 2, 2018

Jim Knicely authored this tip. Use the built-in Vertica function GET_TABLE_PROJECTIONS to quickly summarize the details of all of the projections of a Vertica table. dbadmin=> \t Showing only tuples. dbadmin=> SELECT get_table_projections('public.jim'); Current system K is 1. # of Nodes: 3. Table public.jim has 3 projections. Projection Name: [Segmented] [Seg Cols] [# of Buddies]...

Work hard, have fun, and make a difference!

Sarah Lemaire, Manager, Vertica Documentation
August 2, 2018

We love this 2014 blog post and our intern program, so we're bringing it back to the top of the blog. Jaimin is still here at Vertica, working hard on the core of our analytics database. Watch this space! My name is Jaimin and I work as a Software Engineer in the Distributed Query Optimizer...

Permanently Attach a Comment to a Query: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
August 2, 2018

Jim Knicely authored this tip. There are several system tables like QUERY_REQUESTS that store the queries executed in the database. To help understand why a query was executed (i.e., for debugging purposes), you might want to add a comment to the SQL code. dbadmin=> SELECT request FROM query_requests WHERE transaction_id = current_trans_id() AND statement_id =...

Comparing Two Times: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 30, 2018

Jim Knicely authored this tip. There are several cool built-in functions that you can use to compare two times. Have fun!

Avoid a Division by Zero Error with the NULLIF and NULLIFZERO Functions: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 30, 2018

Jim Knicely authored this tip. The NULLIF built-in Vertica function compares two expressions. If the expressions are not equal, the function returns the first expression. If the expressions are equal, the function returns NULL. The NULLIFZERO built-in Vertica function evaluates to NULL if the value in the column is 0. Both come in handy when...
Three 3D arrows, different colors pointing in different directions

Determining Candidate Segmentation Keys: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 26, 2018

Jim Knicely authored this tip. Hash segmentation allows you to segment a projection based on a built-in hash function that provides even distribution of data across multiple nodes, resulting in optimal query execution. In a projection, the data to be hashed consists of one or more column values, each having a large number of unique...
Modern Database Analytics

Comparing Two Dates: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 25, 2018

Jim Knicely authored this post. There are several cool built-in functions that you can use to compare two dates. Have fun!

Table Last Accessed

Sarah Lemaire, Manager, Vertica Documentation
July 24, 2018

Jim Knicely authored this post. You can query the data collector table DC_PROJECTIONS_USED to ascertain when a table was last accessed and by whom. Helpful Links: PROJECTION_USAGE Querying Data Collector Tables Have fun!

Determining the Per-Node Storage of a Segmented Table

Sarah Lemaire, Manager, Vertica Documentation
July 23, 2018

Jim Knicely authored this blog. You can join a few system tables together to find the disk space used and the row counts by node for segmented tables: Skew in the data distribution across the nodes is an indicator of a poor choice of the table's segmentation key. Have fun!
Database Server Room

How many seconds since midnight?

Sarah Lemaire, Manager, Vertica Documentation
July 19, 2018

Jim Knicely authored this post. There are many reasons you might want to know the number of seconds that have passed since midnight (i.e. Event Logging). You could combine several Vertica built-in date manipulation functions to calculate the number of seconds since midnight, but the easiest way is to use the MIDNIGHT_SECONDS function! The MIDNIGHT_SECONDS...

Changing the Default Database Time Zone

Sarah Lemaire, Manager, Vertica Documentation
July 18, 2018

Jim Knicely authored this tip. When you first install Vertica, the default database time zone is controlled by the TZ environment variable. If TZ is undefined, Vertica uses the operating system time zone. You can change the default database time zone later by altering the value of the TZ environment variable or the operating system...