Referencing Multiple Related LONG VARCHAR Columns

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser

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 you 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!

Sign In or Register to comment.