Options

COPY LOAD default columns

Navin_C

Navin_C Vertica Customer ✭

June 2013 in General Discussion

Hi, This may sound the simplest of all, but still I am a bit stuck on here I am trying to use the COPY utility to load a table. Below is the table structure Code: Select all => \d+ ntab; List of Fields by Tables Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key --------+-------+----------+--------------+------+-------------+----------+-------------+------------- tempdb | ntab | n_id | int | 8 | | t | f | tempdb | ntab | n_err_dt | varchar(30) | 30 | | t | f | tempdb | ntab | m_msg | varchar(255) | 255 | | f | f | tempdb | ntab | n_rsn | varchar(200) | 200 | | f | f | tempdb | ntab | load_dt | date | 8 | "sysdate"() | t | f | (5 rows) I have a flat file with records like this Code: Select all 14|2009-06-25 10:12:22|Active|Cable Now when I try to execute it, it says few columns found and cannot execute the COPY statement I don't understand, if the column is stated not null and it is given the default as sysdate(), then why is it not taking the value. Any workaround for this... Please correct me, If I am understanding it wrong

0

Comments

Options
[Deleted User]

June 2013

Hi Navin, COPY by default assumes that you intend to load all columns in a table, and will reject records where records for each column are not present. (This is intended to be robustness-by-default -- if you did mean to load all columns, we don't want to guess at which columns you expect; we want to error out if one of them is not present, so that we don't give you corrupt records.) There are several ways to loosen this restriction. If you intend a data file to only contain a subset of the columns in a file, and if you have a non-null DEFAULT, then the best way is to explicitly specify that subset: COPY t (n_id, n_err_dt, m_msg, n_rsn) FROM '/path/to/data.txt'; Adam

0

Leave a Comment