Extracting Characters from the Left and Right Side of a LONG VARCHAR: Quick Tip

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 […]

Determining the Current K-Safety: Quick Tip

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=> […]

Specifying CASCADE When Dropping a Table: Quick Tip

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 […]

Ordering the List of Values Returned from LISTAGG

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 | […]

Ignore NULL Values in Non-Correlated Subqueries: Quick Tip

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. […]

Drop More than One Table at a Time: Quick Tip

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 […]

Allowing Users to Query a View Owned by Another User: Quick Tip

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 […]

A View Owner Needs Access to the Underlying Objects: Quick Tip

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; […]

Preserving Objects Owned by a Dropped User: Quick Tip

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 […]

List Table Rows by ROS Container: Quick Tip

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 […]