Vertica Blog

Vertica Blog

Best Practices

Display View Column Data Types: Quick Tip

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: Have fun!

Insert Text Into Text: Quick Tip

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: Have fun!

Reset Your Session: Quick Tip

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: Have fun!

Conveniently Display Key Projection Info: Quick Tip

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: Have fun!

Checking the Validity of a Table Audit: Quick Tip

Jim Knicely authored this tip. The Vertica AUDIT function can be used to estimate the raw data size of a database, schema, or table. Ever wonder if it’s accurate? A simple test shows that it is 100 percent accurate if you specify an error tolerance of 0 and a confidence level of 100. First let’s...

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

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

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

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: Have...
Three 3D arrows, different colors pointing in different directions

Change the VSQL Result Set Border: Quick Tip

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

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: Have fun!
Programmer

Find Which Tables Have a Particular Column Name: Quick Tip

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

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: Have fun!