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
Last modified date: 04/03/2024