How to copy CSV into table with missing column?

Question

I want to import data from a csv file into redshift. CSV format:

col1 , col2 , col3 , col4
 -   ,  -   ,  -   ,  -
 -   ,  -   ,  -   ,  -

Here is my import command:

COPY myTable
FROM 'file.csv'
CSV 
DELIMITER AS ','
IGNOREHEADER AS 1
;

The problem I have is that sometimes, my file has only col1, col2 and col3. Is it possible to execute the COPY and add null for the missing values?

Answer 1

Yes, but must be explicit about it:

COPY mytable (col1, col2, col3)
FROM 'file.csv'
(FORMAT 'csv', HEADER);

The missing col2 will be filled with its default value (NULL unless you defined one).