How to Skip a column when loading data in Vertica

With the help of the options that copy offers we have the ability to skip a column or ever a part of a column. In this examples will demonstrate how this can be done. We will use a csv data files called data.csv as our loading data.

1,1,1
2,2,2
3,3,3

 

And the table we load data into is called test and is has 3 columns.
 create table test(col1 int, col2 int, col3 int);

 Let's see how we can skip a column when loading data.

copy test(
v1 filler int ,
v2 filler int ,
v3 filler int ,
col1 as v1,
col3 as v3) from '/home/dbadmin/data.csv' delimiter ',' direct;

-- query the table
(dbadmin@:5433) [dbadmin]  select * from test;
 col1 | col2 | col3
------+------+------
 1 | | 1
 2 | | 2
 3 | | 3
(3 rows)
  • you can see that no data was loaded into column col2, also is important that the col2 should accept null values.
As you can see we declare the input value as v2 but we don't tell Vertica to pass it into our load batch.

 How can we skip a part of a column 

  • The data we will use for this example:
1x1,1,1
1x2,2,2
1x3,3,3
  •  And the same table test:
 create table test(col1 int, col2 int, col3 int);
Load Example
 copy test(
   v1 FILLER varchar(10) delimiter 'x',
   v2 FILLER int,
   v3 FILLER int,
   v4 FILLER int,
   col1 as v2,
   col2 as v3,
   col3 as v4
   )
   from '/home/dbadmin/data.csv' delimiter ',' direct;

 Rows Loaded
-------------
           3
(1 row)

-- query table
(dbadmin@:5433) [dbadmin]  select * from test;
 col1 | col2 | col3
------+------+------
    1 |    1 |    1
    2 |    2 |    2
    3 |    3 |    3
(3 rows)

-- see content of the load file
(dbadmin@:5433) [dbadmin] * ! cat /home/dbadmin/data.csv
1x1,1,1
1x2,2,2
1x3,3,3
  • you see that we "declare" 4 variables that will receive the input data from the data.csv file but we only pass to the load command only 3 of them. Since we have established that the separator between the first and second variable data set is 'x' , the data will separated into 4 columns.
This can be very useful when loading data.