Vertica Blog

The LEFT SQL function returns the specified characters from the left side of a string, while the RIGHT SQL function returns the specified characters from the right side of a string. Example: dbadmin=> SELECT left(‘Vertica is cool!’, 7), right(‘Everybody loves Vertica’, 7); left | right ——–+——— Vertica | Vertica (1 row) Unfortunately, the LEFT and […]

K-safety sets the fault tolerance in your Vertica database cluster. The value K represents the number of times the data in the database cluster is replicated. These replicas allow other nodes to take over query processing for any failed nodes. You can view the current K-Safety of a Vertica database in several ways. Example: dbadmin=> […]

The DROP TABLE command removes a table and its projections. Example: dbadmin=> CREATE TABLE test1 (c INT); CREATE TABLE dbadmin=> INSERT INTO test1 SELECT 1; OUTPUT ——– 1 (1 row) dbadmin=> SELECT projection_name, create_type FROM projections WHERE anchor_table_name = ‘test1’; projection_name | create_type —————–+—————— test1_super | DELAYED CREATION (1 row) dbadmin=> DROP TABLE test1; DROP […]

The LISTAGG function transforms non-null values from a group of rows into a list of values that are delimited by a configurable separator. LISTAGG can be used to de-normalize rows into a string of comma-separated values or other human-readable formats. Example: dbadmin=> SELECT * dbadmin-> FROM valrank; id | rank | valname —+——+——— 1 | […]

A subquery is a SELECT statement embedded within another SELECT statement. The embedded subquery is often referenced as the query’s inner statement, while the containing query is typically referenced as the query’s statement, or outer query block. A subquery returns data that the outer query uses as a condition to determine what data to retrieve. […]

The DROP TABLE command removes a table and its projections. You can drop more than one table at a time by specifying a comma delimited set of tables! Example: dbadmin=> CREATE TABLE test1 (c INT); CREATE TABLE dbadmin=> DROP TABLE test1; DROP TABLE That was easy. How about dropping more than one table? dbadmin=> CREATE […]

As we learned in yesterday’s Vertica Quick Tip A View Owner Needs Access to the Underlying Objects, the owner of a View must have direct access to the underlying objects referenced by the view! But what if another user wants to read a view owned by another user? In this case, not only must the […]

Jim Knicely authored this tip. You can use the ALTER VIEW … OWNER TO command to change a Vertica database view’s ownership. It’s important to know that a View’s owner must also have access to the underlying objects referenced by the view! Exanple: dbadmin=> SELECT user; current_user ————– dbadmin (1 row) dbadmin=> CREATE SCHEMA test; […]

Jim Knicely authored this tip. When you drop a user with the CASCADE option, all objects owned by that user are lost forever! If you’d prefer to save those objects, you first set the GlobalHeirUserName security parameter to a user who will inherit objects after their owners are dropped. This setting ensures preservation of data […]

Jim Knicely authored this tip. A ROS (Read Optimized Store) container is a set of rows stored in a particular group of files. ROS containers are created by operations like Moveout or COPY DIRECT. You can query the STORAGE_CONTAINERS system table to see ROS containers. You can use the LEAD_STORAGE_OID function to list the rows […]