COPY Restrictions
COPY considers the following data invalid:
- Missing columns (an input line has fewer columns than the recipient table).
- Extra columns (an input line has more columns than the recipient table).
- Empty columns for an INTEGER or DATE/TIME data type. If a column is empty for either of these types, COPY does not use the default value that was defined by the CREATE TABLE command. However, if you do not supply a column option as part of the COPY statement, the default value is used.
- Incorrect representation of a data type. For example, trying to load a non-numeric value into an INTEGER column is invalid.
When COPY encounters an empty line while loading data, the line is neither inserted nor rejected, but COPY increments the line record number. Consider this behavior when evaluating rejected records. If you return a list of rejected records and COPY encountered an empty row while loading data, the position of rejected records is incremented by one.
When loading compressed files, COPY might abort and report an error, if the file seems to be corrupted. For example, this behavior can occur if reading the header block fails.
If any primary key, unique key, or check constraints are enabled for automatic enforcement, Vertica enforces those constraints when you insert values into a table. If a violation occurs, Vertica rolls back the SQL statement and returns an error. This behavior occurs for INSERT
, UPDATE
, COPY
, and MERGE
SQL statements. Note that automatic constraint enforcement requires that you have the SELECT privilege on the table containing the constraint.