COPY Restrictions

Invalid Data

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 CREATE TABLE. 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.

Constraint Violations

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.

Empty Line Handling

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 not incremented by one, as demonstrated in the following example.

--- Load these values into a table that defines the first column as INT.
--- Errors on lines 3, 4, and 8:
=> \! cat -n /home/dbadmin/test.txt 
     1 1|A|2 
     2 2|B|4 
     3 A|D|7 
     4 A|E|7 
     5 
     6 
     7 6|A|3 
     8 B|A|3 

--- Empty rows 5 and 6 shift the reporting of the error on row 8.
=> SELECT row_number, rejected_data, rejected_reason FROM test_bad;
 row_number | rejected_data | rejected_reason 
------------+---------------+---------------------------------------------- 
          3 | A|D|7 | Invalid integer format 'A' for column 1 (c1) 
          4 | A|E|7 | Invalid integer format 'A' for column 1 (c1) 
          6 | B|A|3 | Invalid integer format 'B' for column 1 (c1) 
(3 rows) 

Compressed File Errors

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.