Vertica Blog

Vertica Blog

SQL

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

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

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

Changing the Owner of a View: Quick Tip

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

Jim Knicely authored this tip. We can change the owner of a database view with the ALTER VIEW … OWNER TO command. dbadmin=> INSERT INTO test SELECT 1; OUTPUT -------- 1 (1 row) dbadmin=> CREATE VIEW test_vw AS SELECT c1 FROM test; CREATE VIEW dbadmin=> SELECT table_schema, table_name, owner_name dbadmin-> FROM views dbadmin-> WHERE table_name...

Setting Variables on the VSQL Command Line: Quick Tip

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

Jim Knicely authored this post. The vsql client provides variable substitution features similar to common Linux command shells. Variables are name/value pairs, where the value can be a string of any length. The -v, --variable, and --set options can be used to set a variables value from the command line. Example: The following simple SQL...
Three 3D arrows, different colors pointing in different directions

Exiting a DbVisualizer Script Following an Error: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 19, 2018

After reading yesterday’s Vertica Quick Tip “Exiting a vsql Script Following an Error”, a client asked if the ON_ERROR_STOP variable is available in the popular third party Vertica client tool DbVisualizer. The answer to that is no, as ON_ERROR_STOP is a Vertica vsql client specific setting. However, many clients, including DbVisualizer, have a similar feature!...

The Parts of a Session ID: Quick Tip

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

Jim Knicely authored this post. A Vertica Session is an occurrence of a user interacting with a database through the use of SQL statements. Each Session has an associated Session Identifier stored in the SESSION_ID column of many of the Vertica system tables. The Session ID is a unique identifier within the cluster at any...

Mimicking Enumerated Types: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 6, 2018

I used to work a lot with MySQL. It had a cool data type called "Enumerated Types". Example in MySQL: How do we do this in Vertica? With a Check Constraint! Example in Vertica: Helpful link: https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Constraints/ConstraintTypes/CheckConstraints.htm Have fun!

Changing the Field Separator in VSQL: Quick Tip

James Knicely, Vertica Field Chief Technologist
September 5, 2018

vsql is a character-based, interactive, front-end utility that lets you type SQL statements and see the results. It’s very common to want to export data in CSV (Comma-Separated Values) format. To do that you can change the default | (vertical bar) field separator to a comma via the fieldsep option of the pset meta-command. Wait...
Small chalkboard on a desk with Thank You written on it

Thanks, Google!

Joy King, VP Product & GTM Strategy, Vertica
July 31, 2018

This week began with a compliment from Google that made me so proud on behalf of Vertica! On Wednesday, July 25, at the Google Cloud Next 2018 event, Google launched a Machine Learning (ML) beta with two algorithms – linear and logistic regression – and also confirmed that these new machine learning functions could be...

Changing the Data Type of a Column in an External Table: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 28, 2018

Jim Knicely authored this tip. External tables let you query data stored in files that are accessible to the Vertica database, but not managed by it. When you create the external table, you have to provide column names along with their data types. What happens if you get a data type incorrect? Luckily, you can...
Three 3D arrows, different colors pointing in different directions

Handling NULL Equality in a WHERE Clause: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 25, 2018

Jim Knicely authored this post. The predicate SQL element (i.e., the WHERE clause) is a truth-test. If the predicate test is true, it returns a value. Each predicate is evaluated per row, so that when the predicate is part of an entire table SELECT statement, the statement can return multiple results. Sometimes you might want...

Introducing the VerticaPy Library for Jupyter Notebooks

Soniya Shah, Information Developer
June 21, 2018

One of the coolest things about working at Vertica is our amazing intern program, which often leads to full-time hires. Last year, the VerticaPy library, also known as vpython, was started as an internship project by Badr Ouali. A year later, he works for Vertica full time and has seen his project through into an...

Faster CTAS Statements: Quick Tip

Sarah Lemaire, Manager, Vertica Documentation
June 20, 2018

Jim Knicely authored this tip. In a CREATE TABLE statement, you can specify an AS clause to create a table from a query (a.k.a. CTAS statement). When dealing with a large SELECT statement result set, your CTAS should perform much better if you specify the DIRECT load method! dbadmin=> SELECT TO_CHAR(COUNT(*), '999,999,999,999') row_count FROM smaller_table;...