IBM PureData System for Analytics, Version 7.1

Column constraint rules for empty strings

For all char(n) and varchar(n) data types, the result of inserting an empty string and entering missing data values depends on whether the columns are declared null-able (default) or not null-able (declared with constraint not null). The following table describes the different cases.

Table 1. Column constraining rule for empty strings
Data type	Column constraint	Null token exists		Null token does not exist
		null token	'' (empty string)	'' (empty string)
Char/Nchar Varchar/Nvarchar	NULL	NULL	char/nchar: space filled. varchar/nvarchar: Zero length string.	NULL
Char/Nchar Varchar/Nvarchar	NOT NULL	ERROR	char/nchar: space filled. varchar/nvarchar: Zero length string.	ERROR
Bool, Date, Int (1,2,4,8), Numeric(), Float (4,8), Time, Timestamp, Timetz	NULL	NULL	NULL	NULL
	NOT NULL	ERROR	ERROR	ERROR

If the record contains fewer data values than the actual columns defined in the schema of the table, the system writes an error to the nzlog file and discards the record. To override this behavior, use the -fillRecord option, which applies to the entire load operation.

The -fillRecord option tells the system to use a null value in place of any missing fields. You can use this option if the columns whose values are missing allow nulls. If these columns are defined as not null, the system writes an error to the nzlog file and discards the record. You must resolve this conflict by changing the schema to allow null values or modifying the data file to include a valid non-null value.