Referencing Multiple Related LONG VARCHAR Columns

Posted August 7, 2018 by Sarah Lemaire, Manager, Vertica Documentation

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 test (c1 LONG VARCHAR(32000000), c2 LONG VARCHAR(32000000)); ERROR 4630: Row size exceeds MaxRowSize: 64000024 > 32768000 dbadmin=> CREATE TABLE test1 (c INT, c1 LONG VARCHAR(32000000)) ORDER BY c SEGMENTED BY HASH(c) ALL NODES; CREATE TABLE dbadmin=> CREATE TABLE test2 (c INT, c2 LONG VARCHAR(32000000)) ORDER BY c SEGMENTED BY HASH(c) ALL NODES; CREATE TABLE dbadmin=> INSERT INTO test1 SELECT 1, ‘Some big data text!’; OUTPUT ——– 1 (1 row) dbadmin=> INSERT INTO test2 SELECT 1, ‘Even more big data text!’; OUTPUT ——– 1 (1 row) dbadmin=> COMMIT; COMMIT dbadmin=> CREATE VIEW test_vw AS SELECT test1.c, test1.c1, test2.c2 FROM test1 JOIN test2 USING (c); CREATE VIEW dbadmin=> SELECT * FROM test_vw; c | c1 | c2 —+———————+————————– 1 | Some big data text! | Even more big data text! (1 row) Have fun!