Skipping the columns during data load to Vertica using COPY command

Question

I have situation like my file can have more or less columns than the columns in the table. In copy command column names are fixed that it represent all the columns in the table I cant reduce/increase the columns.

1. When I have less data in the file:

File Data: "FC1"|"FC2"

COPY COPY_TEST(TC1,TC2,TC3,TC4) FROM LOCAL 'COPYTEST_LESS_COL' DELIMITER '|' ENCLOSED BY '"' AUTO;

Gives error as "rejected (Too few columns found)"

NOTE: > I have tried by keeping the default values for the columns in table still the same error > FILLER is not supporting :

COPY COPY_TEST(TC1,TC2,TC3 FILLER VARCHAR(255),TC4 FILLER VARCHAR(255)) FROM LOCAL 'COPYTEST_LESS_COL' DELIMITER '|' ENCLOSED BY '"' AUTO; ERROR 2671: Column reference "TC3" is ambiguous DETAIL: Reference "TC3" could refer to either "COPY_TEST.TC3" or "FILLER.TC3"

2. When I have more data in the file:

File Data: "FC1"|"FC2"|"FC3"|"FC4"|"FC5"|"FC6"|"FC7"

COPY COPY_TEST(TC1,TC2,TC3,TC4) FROM LOCAL 'COPYTEST_More_COL' DELIMITER '|' ENCLOSED BY '"' AUTO;

Gives error as "rejected (Too many columns found)"

I need any alternative solution to load data.

Answer 1

If the columns change considerably, consider reading this part of the Vertica documentation - on flex tables:

https://docs.vertica.com/23.4.x/en/flex-tables/

If the columns at the beginning are stable, however, and only at the end you might have one, two or three fields added occasionally to the otherwise stable file structure, try this scenario - where I added the data in-line in a sql file to be executed using vsql:

-- start of script
DROP TABLE IF EXISTS copy_test;
CREATE TABLE copy_test(
   scen VARCHAR(32)
 , tc1  VARCHAR(8)
 , tc2  VARCHAR(8)
 , tc3  VARCHAR(8)
 , tc4  VARCHAR(8)
);
COPY COPY_TEST(
  scen
, TC1
, TC2
, tc3
, tc4
, tc5 FILLER CHAR(3)
, tc6 FILLER CHAR(3)
, tc7 FILLER CHAR(3)
) FROM LOCAL STDIN  DELIMITER '|' ENCLOSED BY '"' 
SKIP 1
TRAILING NULLCOLS
EXCEPTIONS '/dev/tty';
scen| tc1 | tc2 | tc3 | tc4
trailing nullcols|"FC1"|"FC2"
trailing nullcols|"FC1"|"FC2"|"FC3"
all columns filled|"FC1"|"FC2"|"FC3"|"FC4"
too many fields|"FC1"|"FC2"|"FC3"|"FC4"|"FC5"|"FC6"|"FC7"
too many fields|"FC1"|"FC2"|"FC3"|"FC4"|"FC5"|"FC6"
too many fields|"FC1"|"FC2"|"FC3"|"FC4"|"FC5"
\.
\pset null (null)
SELECT * FROM copy_test;
-- end of script

What you get is this:

scen	tc1	tc2	tc3	tc4
all columns filled	FC1	FC2	FC3	FC4
too many fields	FC1	FC2	FC3	FC4
too many fields	FC1	FC2	FC3	FC4
too many fields	FC1	FC2	FC3	FC4
trailing nullcols	FC1	FC2	FC3	(null)
trailing nullcols	FC1	FC2	(null)	(null)