Vertica Blog

Vertica Blog

Quick Tips

Calculate Request Queue Length: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 7, 2018

The RESOURCE_ACQUISITIONS system table retains information about resources (memory, open file handles, threads) acquired by each running request. Each request is uniquely identified by its transaction and statement IDs within a given session. From this system table, you can calculate how long a request was queued in a resource pool before acquiring the resources it...

Concatenate non-NULL Values from a Group: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 6, 2018

Vertica 9.1.1-4 introduces an extremely useful aggregate function named LISTAGG, which returns a string with concatenated non-NULL values from a group. Have fun!

Simplify String Literals with Dollar-Quoted String Literals: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 6, 2018

The standard syntax for specifying string literals can be difficult to understand. To allow more readable queries in such situations, Vertica SQL provides dollar quoting. Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant single quote syntax. Helpful Link:...

Re-Compute a Table Column’s Default Value Immediately: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 1, 2018

Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. That DEFAULT expression can be derived from another column in the same table! When you update the value in a base column, you will need to re-compute the value in your derived column (the...
Modern Database Analytics

Re-Compute a Table Column’s Default Value: Quick Tip

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

Jim Knicely authored this tip. You can specify a table column's default value using a DEFAULT expression. Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. That DEFAULT expression can be derived from another column in the same table! To see how, check...
Programmer

Before Upgrading: Identify and Remove Unsupported Projections

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

With Vertica 9.1 and 9.2, Vertica has removed support for the following projections: Vertica 9.1: Projection buddies with different SELECT and ORDER BY clauses. All projection buddies must specify columns in the same order. The Vertica database regards projections with non-compliant buddies as unsafe. Vertica 9.2: Pre-join and range segmentation projections. If a table's only...

Derive a Table Column’s Default Value from another Column: Quick Tip

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

Jim Knicely authored this tip. You can specify a table column's default value using a DEFAULT expression. To see how, check out this quick tip. When you set a default value, Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. In addition, that...

Default a Column’s Value: Quick Tip

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

Jim Knicely authored this tip You can specify a table column's default value using a DEFAULT expression. If the operation omits a value for the column, Vertica evaluates the DEFAULT expression and sets the column on load operations. Helpful Links: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/column-constraint.htm Have fun!

Display View Column Data Types: Quick Tip

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

Jim Knicely authored this tip. Like table columns, view columns also have a data type. You can display the data types of view columns by querying the VIEW_COLUMNS system table. Helpful Links: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/VIEW_COLUMNS.htm https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATEVIEW.htm Have fun!
Database Server Room

Insert Text Into Text: Quick Tip

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

Jim Knicely authored this tip. The built-in Vertica INSERT function injects a character string into a specified location in another character string. Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/INSERT.htm Have fun!

Reset Your Session: Quick Tip

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

Jim Knicely authored this tip. The SHOW command “shows” run-time parameters for the current session. You can modify one or more of these parameters with the SET command. To quickly reset all of the run-time parameters back to their default values, execute the RESET_SESSION function! Helpful Links: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/SHOW.htm https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/SET/SETSESSIONRUNTIMECAP.htm https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/RESET_SESSION.htm Have fun!

Conveniently Display Key Projection Info: Quick Tip

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

Jim Knicely authored this tip. The Vertica GET_PROJECTION_STATUS function can be called to quickly display key projection facts such as segmentation columns, verified fault tolerance, and statistics status. Examples: Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/GET_PROJECTION_STATUS.htm Have fun!