Skipping the columns during data load to Vertica using COPY command

Phantom

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 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)"

marcothesane

What you need to do is reference all columns you want to load into the table, in the order they appear in the file, apart from the columns you have no data for, which you could initialise with a default expression. Like here:

CREATE TABLE copy_test(
   tc1 VARCHAR(8)
  ,tc2 VARCHAR(8)
  ,tc3 VARCHAR(8)
  ,tc4 VARCHAR(8)
);
CREATE TABLE

COPY COPY_TEST(
  TC1
, TC2
, TC3 AS 'default'
, TC4 AS 'default'
) FROM LOCAL STDIN  DELIMITER '|' ENCLOSED BY '"' AUTO;
 "FC1"|"FC2"
\.
 Rows Loaded 
-------------
           1
(1 row)

SELECT * FROM copy_test;
 tc1 | tc2 |   tc3   |   tc4   
-----+-----+---------+---------
 FC1 | FC2 | default | default
(1 row)