Vertica Blog

Vertica Blog

Best Practices

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

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

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

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

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!

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: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/INSERT.htm 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: 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

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!

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

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