Vertica Blog

Vertica Blog

DBadmin

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

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

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

Counting Days, Week Days and Weekend Days Across the Years: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 17, 2018

Jim Knicely authored this post. By combing Vertica’s built-in date formatting functions and the TIMESERIES clause, it’s easy to find the total number of days, total number of week days (Monday - Friday), and the total number of weekend days (Saturday - Sunday) that occur within a multi-year range (i.e., 2016 - 2018), including Leap...

Changing the Data Type of a Column in an External Table: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 28, 2018

Jim Knicely authored this tip. External tables let you query data stored in files that are accessible to the Vertica database, but not managed by it. When you create the external table, you have to provide column names along with their data types. What happens if you get a data type incorrect? Luckily, you can...

Limiting a User’s Open Session Count: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 27, 2018

Jim Knicely authored this tip. By default, a user can have an unlimited number of connections across the database cluster. Type: \h or \? for help with vsql commands \g or terminate with semicolon to execute query \q to quit jim=> \! vsql -U jim -w 'pw' Welcome to vsql, the Vertica Analytic Database interactive...

NULL Equals NULL with NULLSEQUAL: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 27, 2018

Jim Knicely authored this tip. The Vertica CASE expression is a generic conditional expression that can be used wherever an expression is valid. It is similar to case and if/then/else statements in other languages. What if we are comparing a NULL with a NULL? Remember that in Vertica, a NULL can never equal anything. But...

Skipping Records with Unspecified JSON Fields

Phil Molea, Sr. Information Developer, Vertica
June 26, 2018

Serge Bonte and Jim Knicely authored this post. Vertica provides a built-in file parser named FJSONPARSER that parses and loads a JSON file. This file can contain either repeated JSON data objects (including nested maps) or an outer list of JSON elements. For a flex table, the parser stores the JSON data in a single-value...
Three 3D arrows, different colors pointing in different directions

Handling NULL Equality in a WHERE Clause: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 25, 2018

Jim Knicely authored this post. The predicate SQL element (i.e., the WHERE clause) is a truth-test. If the predicate test is true, it returns a value. Each predicate is evaluated per row, so that when the predicate is part of an entire table SELECT statement, the statement can return multiple results. Sometimes you might want...
Programmer

Be Careful with the Sequence CACHE Value

Sarah Lemaire, Manager, Vertica Documentation
June 21, 2018

Jim Knicely authored this tip. The default session cache for a sequence is 250,000. Although you can change the cache value of a sequence, setting the value too low can adversely affect performance! Note that it took 45 seconds to generate 10,000 new sequence values on a 3-node Vertica cluster where sequence cache was 5,...

Faster CTAS Statements: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 20, 2018

Jim Knicely authored this tip. In a CREATE TABLE statement, you can specify an AS clause to create a table from a query (a.k.a. CTAS statement). When dealing with a large SELECT statement result set, your CTAS should perform much better if you specify the DIRECT load method! dbadmin=> SELECT TO_CHAR(COUNT(*), '999,999,999,999') row_count FROM smaller_table;...