Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

COPY LOAD default columns

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


  • 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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.