With Null Clause for COPY
When copying data from a table to a file, the with null clause directs copy to put the specified value in the file in place of null fields. You must specify the with null clause for any column that is nullable; if you omit the with null clause, the DBMS Server returns an error when it encounters null data, and aborts the copy statement.
When copying data from a file to a table, the with null clause specifies the value in the file to be interpreted as a null. When copy encounters this value in the file, it writes a null to the corresponding table column.
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 you choose to represent nulls must be compatible with the format of the field in the file. Character formats require quoted values and numeric formats require unquoted numeric values.
For example, the following example is incorrect, because the value specified for nulls (numeric zero) conflicts with the character data type of the field:
Wrong:
c0comma with null(0)
The following example, however, is correct:
Right:
c0comma with null("0")
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 field format you specify is at least as large as the value you specify 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:
Wrong:
copy table t1 (col1 = varchar(1) with null ("NULL")) into "t1.dat"
The correct version specifies a 4-character format for the column.
Right:
copy table t1 (col1 = varchar(4) with null ("NULL")) into "t1.dat"
If you specify with null but omit value, copy appends a trailing byte indicating whether the field is null. For null fields, copy inserts an undefined data value in place of the null and sets the trailing byte to indicate a null field. You must specify value for nullable char(0) and varchar(0) columns.