Restrictions and Considerations
COPY cannot be used to add data to a view, index, or system catalog.
When copying data into a table, COPY ignores any integrity constraints defined (using the CREATE INTEGRITY statement) against the table.
When copying data into a table, COPY ignores ANSI/ISO Entry SQL-92 check and referential constraints (defined using the CREATE TABLE and ALTER TABLE statements), but does not ignore unique (and primary key) constraints.
The COPY statement does not fire any rules defined against the table.
Values cannot be assigned to SYSTEM_MAINTAINED logical key columns. The DBMS Server assigns values when copying from a data file to a table. This occurs even if the logical key column is being implicitly loaded using an unformatted copy; the values in the data file are ignored, and new values assigned by the DBMS Server.
COPY treats a present-but-empty field in the data file as:
• A blank default for character-based columns
• A zero default for numeric columns
• December 31 00-1 for date and timestamp columns
• 00:00:00 for time columns (and the time component in a timestamp)
For example, if you load a data file (COPY FROM) that contains a record with one empty value into a table with a mandatory (NOT DEFAULT) integer field, the load of the one record does not fail due to the missing value, but succeeds and loads a 0. You do get the error for the missing value if the column is omitted from the copy list. Note that if the table has a column defined with a default value and the field in the data file is present but empty, COPY loads the default as listed above. If you omit the column from the copy list then you will get the proper default value.