5. Populating Tables : Formatted Copying : COPY Statement and Nulls
 
Share this page                  
COPY Statement and Nulls
When you copy data from a table to a file or vice versa, the WITH NULL clause of the COPY statement allows you to substitute a value for nulls.
When you use variable length data formats when copying, you must replace the null values with some string that represents nulls; for example:
COPY TABLE personnel (name=CHAR(20),
    salary=CHAR(0) WITH NULL ('N/A'),
    dummy=d0nl)
    INTO 'pers.data';
After executing this statement, the pers.data file contains “N/A” for each null salary.
With other data formats, you are not required to substitute a value for nulls. However, if you do not, your file contains unprintable characters.
When substituting a value for nulls, the value:
Must not be one that occurs in your data
Must be compatible with the format of the field in the file:
Character formats require quoted values
Numeric formats require unquoted numeric values