Character strings

The char(n) and nchar(n) data types represent character strings of length n. The varchar(n) and nvarchar(n) data types represent variable-length character strings of maximum length n. A valid character has an ASCII value of 32 - 255.

System handling of characters

The following table describes how the system handles char, nchar, varchar, and nvarchar characters:

Table 1. Character handling
Type of change	Characters handling
Padding	Values of char and nchar are padded to normal length with spaces. Values of varchar and nvarchar are not padded.
Truncation	If the data is longer than the field, the following actions take place: The system writes the record to the nzbad file. The system writes a summary of the bad records to the nzlog file. You can turn on automatic truncation by using the -truncString option for the nzload command. Important: If you use this option for Unicode character data, it can truncate combined NFC characters if they exceed the specified column length. The option does not attempt to keep any grapheme clusters; it truncates data as necessary to fit in the specified column size.

Column constraint rules for empty strings

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

Table 2. Column constraint rule for empty strings
Data type	Column constraint	Null token exists		Null token does not exist
		null token	'' (empty string)	'' (empty string)
char and nchar varchar and nvarchar	NULL	NULL	char and nchar: space filled varchar and nvarchar: zero length string	NULL
char and nchar varchar and nvarchar	NOT NULL	ERROR	char and nchar: space filled varchar and 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 columns that you 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 of the nzload command. This option, which applies to the entire load operation, 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 you defined these columns 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 by modifying the data file to include a valid non-null value.