Can we have columns larger than 60K characters in Netezza?

Netezza has a couple of limitations regarding column and row length, being a column maximum of 64K and a row maximum of 65535K, not to mention the column limit of 1600 in a table.
How can we store longer text and display this information ?

The short answer is that you cannot do so. You would have to split over columns, and if need be over tables linked by a key.

NZPOC(NZ)=> create table a (b varchar(64000));
CREATE TABLE
NZPOC(NZ)=> create table a1 (b varchar(64001));
ERROR:  length for type 'varchar' cannot exceed 64000
NZPOC(NZ)=> d a
                            Table "A"
 Attribute |           Type           | Modifier | Default Value
-----------+--------------------------+----------+---------------
 B         | CHARACTER VARYING(64000) |          |
 C         | CHARACTER VARYING(1000)  |          |
 D         | CHARACTER VARYING(400)   |          |
 E         | CHARACTER VARYING(90)    |          |
 F         | CHARACTER VARYING(3)     |          |
Distributed on hash: "B"
NZPOC(NZ)=> alter table a add column (g varchar(1));
ERROR:  Table '_BV_1790158' record size 65536 exceeds internal limit of 65535 bytes

So, can we take two tables and form a view which will hold this data ? The answer is no.

NZPOC(NZ)=> d blg1
                          Table "BLG1"
 Attribute |           Type           | Modifier | Default Value
-----------+--------------------------+----------+---------------
 ROWNUMBER | CHARACTER VARYING(11)    |          |
 OUTSTR    | CHARACTER VARYING(59567) |          |
Distributed on hash: "ROWNUMBER"
NZPOC(NZ)=> d blg2
                          Table "BLG2"
 Attribute |           Type           | Modifier | Default Value
-----------+--------------------------+----------+---------------
 ROWNUMBER | CHARACTER VARYING(11)    |          |
 OUTSTR    | CHARACTER VARYING(46420) |          |
Distributed on hash: "ROWNUMBER"
NZPOC(NZ)=> create view blg as select b1.rownumber, b1.outstr||b2.outstr from blg1 b1, blg2 b2
NZPOC(NZ)-> where b1.rownumber = b2.rownumber;
ERROR:  Table 'BLG' record size 105998 exceeds internal limit of 65535 bytes

Can we select this long row ? Nope.

NZPOC(NZ)=> select b1.rownumber, b1.outstr||b2.outstr from blg1 b1, blg2 b2
NZPOC(NZ)-> where b1.rownumber = b2.rownumber limit 1;
ERROR:  Record size 105998 exceeds internal limit of 65535 bytes

Short answer is not to select anything which exceeds this internal limitation on Netezza. You can however return substrings of this data.

NZPOC(NZ)=> select b1.id_number, substr(b1.outstr,30000,10)||substr(b2.outstr,30000,10)
NZPOC(NZ)-> from blg1 b1, blg2 b2
NZPOC(NZ)-> where b1.id_number = b2.id_number limit 1;

So don’t try to get around these limits.

This entry was posted in Commands, Netezza, nzsql and tagged , , . Bookmark the permalink.