Vertica Blog

Vertica Blog

Best Practices

Generate an Auto-Incrementing Alphabetical Series Using SQL: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 17, 2018

Jim Knicely authored this tip. One of the very first Vertica questions I was asked from a client was how to generate an auto-incrementing alphabetical series using SQL for use as a primary key in a table. For example, the user wanted to generate this result set: We figured out that this was easily done...

Display Null Query Result With an Alternate Text: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 16, 2018

Jim Knicely authored this post. In vsql, the /pset meta-command is used to set options that control how Vertica formats query result output. One of my favorites is the null option which allows you to specify an alternative value for null in the result set of a query. That didn’t work! Make sure to use...

Special Date/Time Formatting: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 15, 2018

Jim Knicely authored this tip. Vertica supports several special date/time values for our convenience. All of these values need to be written in single quotes when used as constants in SQL statements. My favorite is ALLBALLS which is named so because the time digits look like balls (I had to Google that). Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Expressions/DateTimeExpressions.htm...
Three 3D arrows, different colors pointing in different directions

Change the VSQL Result Set Border: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 11, 2018

Jim Knicely authored this tip. vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. The results from vsql are formatted nicely with a border between columns. You are probably used to viewing border style 1, the default, but you may prefer border styles 0 or 2! Helpful...

Find Which System Tables Have a Particular Column Name: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 10, 2018

Jim Knicely authored this tip. There are over 180 built-in Vertica system tables in the V_CATALOG and V_MONITOR schemas. Trying to remember which system tables contain a certain column can be a daunting task. Instead, query the V_CATALOG.SYSTEM_COLUMNS system table! Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/SYSTEM_COLUMNS.htm Have fun!
Programmer

Find Which Tables Have a Particular Column Name: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 9, 2018

Jim Knicely authored this tip. Your Vertica database probably contains hundreds, if not thousands, of tables. Trying to remember which tables contain a certain column can be a daunting task. Instead, query the V_CATALOG.COLUMNS system table! Which tables have a column named “DateKey”? Which tables have a column with a name that contains the text...

Database Storage – Raw and Compressed Size: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 8, 2018

Jim Knicely authored this post. One of the files generated by the /opt/vertica/scripts/collect_diag_dump.sh script details the raw and compressed size for every table in the Vertica database. At the bottom of the report there is a “total” line that shows the database raw and compressed size! Example Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Diagnostics/ExportingProfilingData.htm Have fun!

Display Last SQL Run Time: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 4, 2018

Jim Knicely authored this post. When the \timing meta-command in vsql is set to “on”, Vertica will return how long (in milliseconds) each SQL statement runs. That’s great! But what if you forgot to turn it on prior to executing a long running query? Instead of turning it on then re-executing that long running query,...

Displaying the Administration Tools Process ID: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 3, 2018

Jim Knicely authored this post. From within vsql you can run the Linux ps command to display the Administration Tools process on the local node. However, it’s a lot easier to use the GETPID function. Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Monitoring/Vertica/MonitoringProcessStatusPs.htm Have fun!
Database Server Room

Rotating Column Data Using SQL Analytics: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 2, 2018

Jim Knicely authored this post. Vertica analytics are SQL functions based on the ANSI 99 standard. These functions handle complex analysis, reporting tasks, and allow for some cool data manipulation. I have the following table of names where each name has a unique ID. I want to rotate the values in column NAME up by...

Return Zero Instead of NULL: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
October 1, 2018

Jim Knicely authored this post. The built-in Vertica function ZEROIFNULL evaluates to 0 for NULL values in a table’s column. Whoa! What happened in that last statement? Since NULL by itself does not have a data type, you’ll need to cast it to a valid data type for the function! Helpful link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Null/ZEROIFNULL.htm Have fun!

Exiting a vsql Script Following an Error: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
September 18, 2018

Jim Knicely authored this tip. By default, if a vsql script command results in an error, for example, because of a malformed command or invalid data format, processing continues. If you set ON_ERROR_STOP to “on” in a vsql script and an error occurs during processing, the script terminates immediately. dbadmin=> CREATE TABLE tbl1_temp (pkid INT);...