Converting CHAR and VARCHAR to BINARY or VARBINARY

[Deleted User]

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 FROM test_coercion;
ERROR 2366:  Cannot cast type varchar to varbinary

Using Vertica 9.1.1 and above:

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 FROM test_coercion;
     c1
-------------
Convert me!
(1 row)

Have fun!

Share this: