Testing Netezza SQL: rejecting mismatched datatype values

This illustrates how Netezza responds when rejecting mismatch datatypes. 

Load string value larger than column size:

------------------------------------------------------------------

/* Duration 0.234375 sec. */

create table testVarcharInsert (col1 varchar(10));

/* Duration 0.484375 sec. */

/* Records Affected: 1. */

insert into testVarcharInsert values ( '1234567890');

/* ERROR [HY000] ERROR:  value '1234567890X' too large for column  */  <-- ERROR

insert into testVarcharInsert values ('1234567890X');

------------------------------------------------------------------

Load integer value larger than range defined:

Netezza Limitations: 

INTEGER:  32-bit values in range –2,147,483,648 to 2,147,483,647

/* Duration 0.234375 sec. */

create table testVarcharInsert (col1 integer);

/* Duration 1.25 sec. */

/* Records Affected: 1. */

insert into testVarcharInsert values (12345);

/* ERROR [HY000] ERROR:  pg_atoi: error in "abcd": can't parse "abcd" */ <-- ERROR

insert into testVarcharInsert values ('abcd');

/* ERROR [HY000] ERROR:  int8 conversion to int4 is out of range */ <-- ERROR

insert into testVarcharInsert values (999888777666555444);

------------------------------------------------------------------

Pushing the limits of field length and row size in Netezza table.

Creating tables with fields larger than Netezza limits:

Netezza Limitations: 

Maximum number of characters in a char/varchar field: 64,000

Maximum row size: 65,535 bytes

------------------------------------------------------------------

/* ERROR [HY000] ERROR:  length for type 'varchar' cannot exceed 64000 */ <-- ERROR: over the max length

create table testVarcharLength_FAILS (col1 varchar(64001));

/* ERROR [HY000] ERROR:  Table 'TESTVARCHARLENGTH_FAILS' record size 73999 exceeds internal limit of 65535 bytes */  <-- ERROR: over the max row size limit

create table testVarcharLength_FAILS  (col1 varchar(64000),col2 varchar(9999 ));

/* ERROR [HY000] ERROR:  65536 : Record size limit exceeded */ <-- ERROR: over the max row size limit

create table testVarcharLength_FAILS  (col1 varchar(64000),col2 varchar(1517 ));

/* Duration 0.28125 sec. */  <-- SUCCESS 

create table testVarcharLength_SUCCESS_1 (col1 varchar(64000),col2 varchar(1516 ));

/* Duration 0.21875 sec. */  <-- SUCCESS  

create table testVarcharLength_SUCCESS_2 (col1 varchar(64000));

------------------------------------------------------------------