Vertica Blog

Vertica Blog

SQL

Find the Version of Vertica that Created a Database: Quick Tip

You can run the VERSION() function as one method of displaying the current version of Vertica. But what if you want to know the version of Vertica running when you created the current database? For that info you can query the VS_GLOBAL_SETTINGS table. Helpful Link: Have fun!

Rejected Data Table Row Number: Quick Tip

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause saves rejected data into a table. The rejected data table includes an informative column called ROW_NUMBER where its value indicates the rejected row number from the input file. Be aware that when a COPY encounters an empty line while loading...

Reload Data from a Rejected Data Table: Quick Tip

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause, will save rejected data into a table. If you realize there is a modification to the COPY command that will allow those rejected records to load successfully, you can re-run the updated COPY command against the rejected data in the...

Handling Cast Conversion Load Errors: Quick Tip

The nifty cast ::! returns all cast failures as NULL instead of generating an error if a the data type cannot be coerced. This cast feature, combined with the FILLER option of the COPY command, is very useful for loading data when data types aren’t playing nice. Vertica cannot implicitly cast the NULL value to...

Return All Cast Failures as NULL: Quick Tip

When you invoke data type coercion (casting) by an explicit cast and the cast fails, the result returns either an error or NULL. Cast failures commonly occur when you attempt to cast conflicting conversions, such as trying to convert a varchar expression that contains letters to an integer. However, using the nifty cast ::! instead...

Calculate Request Queue Length: Quick Tip

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

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

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

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

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

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

Viewing Previously Generated Explain Plans: Quick Tip

Jim Knicely authored this post. The EXPLAIN command returns a formatted description of the Vertica optimizer's plan for executing the specified statement. dbadmin=> EXPLAIN SELECT * FROM fact JOIN dim USING (c2); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN DESCRIPTION: ------------------------------ EXPLAIN SELECT * FROM fact JOIN dim USING (c2); Access Path: +-JOIN MERGEJOIN(inputs presorted) [Cost: 25,...

Display the Current Statement Number Within the Current Transaction: Quick Tip

Jim Knicely authored this post. A Transaction in Vertica is one or more operations that are executed as a unit of work. At the user level, transactions occur in the current session by a user or script running one or more SQL statements. A transaction has a unique Transaction ID and the “units of work”...