Referencing Multiple Related LONG VARCHAR Columns

[Deleted User]

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!