Vertica Blog
Sarah Lemaire smiling

Sarah Lemaire

Manager, Vertica Documentation

Connect With Sarah on

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

Synonyms: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 16, 2018

Jim Knicely authored this post. A synonym in Oracle is an alternative name for a database object (i.e., table, view, sequence, etc.). Synonyms work great when you want to point a user to a specify schema when a schema is not specified in a query. Although synonyms are not used in Vertica, you can instead...

Average Date: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 12, 2018

Jim Knicely authored this tip. Given a list of dates, how do I find the “average” date? Like this… dbadmin=> SELECT AVG(JULIAN_DAY(mydate)) + '4714-11-24 BC'::DATE avg_date dbadmin-> FROM dates; avg_date --------------------- 2018-07-11 00:00:00 (1 row) Have fun!

The Case of Object Names: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
July 11, 2018

Jim Knicely authored this tip. When creating objects in Vertica (i.e., tables), Vertica preserves the “case” of the object name in the catalog meta-data exactly as you ask. dbadmin=> SELECT table_name FROM tables WHERE table_name ILIKE '%lower_case%'; table_name ------------ lower_case (1 row) dbadmin=> DROP TABLE lower_case; DROP TABLE dbadmin=> CREATE TABLE LOWER_CASE (c1 INT); CREATE...
A cloud of data points with a businessman's hand holding it up on a black background

Announcing Vertica in Eon Mode for Amazon Linux 2, now available in AWS Marketplace

Sarah Lemaire, Manager, Vertica Documentation
June 28, 2018

Vertica is a blazingly fast SQL analytics database, enabling enterprises to access and derive meaningful insight into big data in sub-seconds or minutes rather than hours or days. Vertica powers the world’s most data driven organizations, delivering unmatched speed and scale with the full suite of advanced analytics and in database machine learning. Vertica for...

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

Analyze Statistics at the Schema Level (Part 1): Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 18, 2018

Jim Knicely wrote this tip. The ANALYZE_STATISTICS function collects and aggregates data samples and storage information from all nodes that store projections associated with the specified table. The function accepts a table/projection/column name as input. What if you wanted to get stats for all of the tables in a schema? One option is to have...