myVertica  

Vertica Blog

Converting CHAR and VARCHAR to BINARY or VARBINARY

Jim Knicely authored this tip. As of Vertica 9.1.1, explicit coercion (casting) from CHAR and VARCHAR data types to either BINARY or VARBINARY data types is supported! Example: Prior to Vertica 9.1.1: dbadmin=> CREATE TABLE test_coercion (c1 VARCHAR(100)); CREATE TABLE dbadmin=> INSERT INTO test_coercion SELECT ‘Convert me!’; OUTPUT ——– 1 (1 row) dbadmin=> SELECT c1::VARBINARY […]

Getting Help with Command-Line Tools

Jim Knicely authored this post. Vertica comes with some nifty command-line tools like vsql, admintools, and vbr. If you need assistance using any of these, you can run them with the –-help parameter to view the options available for each tool. Example: [dbadmin@s18384357 ~]$ /opt/vertica/bin/vbr –help vertica backup and restore script help -h –help print […]

vertica-python Becomes Vertica’s First Officially Supported Open Source Database Client

Tom Wall authored this post. We are very excited to announce that vertica-python, the open source python database client for Vertica created by Uber Technologies Inc., will officially become a supported database client for Vertica! This is a great milestone for Vertica and our community of users and developers. Not only do we expect to […]

Cascading Schema Ownership

Jim Knicely authored this tip. Yesterday’s quick tip revealed that as of Vertica 9.1.1, you can transfer the ownership of a schema to another user. But what about the underlying schema objects (i.e., TABLES, VIEWS, etc.)? By default, the ALTER SCHEMA…OWNER TO command does not affect ownership of objects in the target schema or the […]

Change the Owner of a Schema

Jim Knicely authored this tip. As of Vertica 9.1.1, you can now transfer the ownership of a schema to another user! Example: dbadmin=> SELECT schema_name, schema_owner FROM schemata WHERE schema_name = ‘my_etl_schema’; schema_name | schema_owner —————+————– my_etl_schema | dbadmin (1 row) dbadmin=> CREATE USER etl_user; CREATE USER dbadmin=> ALTER SCHEMA my_etl_schema OWNER TO etl_user; ALTER […]

Find the Number of Days Passed and Remaining in the Relative Year

Jim Knicely authored this post. Although there aren’t any specific functions that will return the number of days that have passed and that are remaining in a given year, you can combine a few of Vertica’s built-in date functions to find these numbers. Example: dbadmin=> SELECT sysdate “Today”, dbadmin-> DATEDIFF(day, TRUNC(sysdate, ‘YY’), TRUNC(sysdate)) “# of […]

Displaying the Current Schema

Jim Knicely authored this post. Each user session has a search path of schemas. Vertica uses this search path to find tables and user-defined functions (UDFs) that are unqualified by their schema name. You can use the CURRENT_SCHEMA function to display the name of the current schema (i.e., the first “valid” schema in the user’s […]

Referencing Multiple Related LONG VARCHAR Columns

Jim Knicely authored this post. Table columns having the LONG VARCHAR data type can store up to 32,000,000 octets. Since there is a table row limit size of 32,768,000 bytes, how do I reference more than one related LONG VARCHAR, each having the maximum length? Simple. Use more than one table! Example: dbadmin=> CREATE TABLE […]

Generate Random Integers, Including Negative Numbers

Jim Knicely authored this tip. The RANDOMINT(n) function returns one of the n integers from 0 through n – 1. Those are all positive integers. What if I want to include negative integers? That’s easy with a simple multiplication. Example: dbadmin=> SELECT DECODE(randomint(2), 1, 1, -1) * randomint(11) “Random INT from -10 to 10”; Random […]

Summarize a Table’s Projections: Quick Tip

Jim Knicely authored this tip. Use the built-in Vertica function GET_TABLE_PROJECTIONS to quickly summarize the details of all of the projections of a Vertica table. Example: dbadmin=> \a Output format is unaligned. dbadmin=> \t Showing only tuples. dbadmin=> SELECT get_table_projections(‘public.jim’); Current system K is 1. # of Nodes: 3. Table public.jim has 3 projections. Projection […]