WITH NULL Clause for COPY
The WITH NULL clause allows the data file to contain NULL data. There are two methods of indicating a NULL: an indicator byte (no value given), or a special user-defined marker value. The indicator byte method does not depend on a particular marker value, but it is a binary form not suited to human readable text files. Also, the indicator byte method is not available for variable-length data fields. The user-defined marker value method works with any file format, but depends on a user chosen value that must not otherwise appear in the data.
WITH NULL (value) Clause
When copying data from a table to a file, the WITH NULL (value) clause directs COPY to put the specified value in the file when a null is detected in the corresponding column. If a null is detected and there is no WITH NULL clause, a runtime error occurs, and aborts the COPY statement.
When copying data from a file to a table, the WITH NULL (value) clause specifies a marker value to be interpreted as a null. When COPY encounters this value in the file, it writes a null to the corresponding table column. The table column must be nullable; if it is not, a runtime error occurs, and aborts the COPY statement.
To prevent conflicts between valid data and null entries, choose a value that does not occur as part of the data in your table. The value chosen to represent nulls must be compatible with the format of the field in the file: character formats require quoted values, and binary numeric formats require unquoted numeric values. For example:
This example of a value is incorrect:
c0comma WITH NULL(0)
because the value specified for nulls (numeric zero) conflicts with the character data type of the field. However, this example is correct:
c0comma WITH NULL('0')
because the null value is character data, specified in quotes, and does not conflict with the data type of the field. Do not use the keyword null, quoted or unquoted, for a numeric format.
When copying from a table to a file, be sure that the specified field format is at least as large as the value specified for the WITH NULL clause. If the column format is too small, the DBMS Server truncates the null value written to the data file to fit the specified format.
For example, in the following statement the string, 'NULL,' is truncated to 'N' because the format is incorrectly specified as one character:
COPY TABLE t1str (col1 = CHAR(1) WITH NULL ('NULL')) INTO 't1.dat';
The correct version specifies a 4-character format for the column.
COPY TABLE t1str (col1 = CHAR(4) WITH NULL ('NULL')) INTO 't1.dat';
WITH NULL Clause Omitting Value
If WITH NULL is specified but value is omitted, COPY uses a trailing indicator byte in the file to determine whether a file value is a null. COPY INTO writes a zero trailing byte if the value is not NULL; it writes a nonzero trailing byte to indicate a NULL. (The value written prior to the indicator byte is undefined if the indicator shows NULL.) COPY FROM reads and interprets a trailing indicator byte in the same manner, zero for not null and nonzero for null. Indicator bytes are not allowed for variable-length formats such as char(0); variable-length formats must specify a value clause.