I am trying to load a Redshift table from a .csv file in an S3 bucket. When I created the table, three columns are set to have default values.
,"current_row_ind" CHAR(1) DEFAULT 'Y'
,"EFFECTIVE_DATE" DATE DEFAULT TO_DATE(CURRENT_DATE, 'YYYY-MM-DD')
,"EXPIRATION_DATE" DATE DEFAULT TO_DATE('9999-12-31', 'YYYY-MM-DD')
Some of the rows in my file have values for these fields. Some do not. When I use the COPY command to load the table, Redshift seems to ignore the defaults for those fields that do not have values filled in. If I don't specify those fields in the list but leave them in my file, I get the following error: "Extra column(s) found". If I do specify those fields in the list, those fields in my file that do not have values are added with NULL values rather than the defaults. If I leave those fields off the COPY list and remove them from my file, they load with the defaults. Obviously this won't work as some of the fields have legitimate values. I've listed the parameters I'm using in my COPY command below:
FORMAT AS CSV DELIMITER ','
QUOTE '"'
ACCEPTINVCHARS '?'
TIMEFORMAT 'auto'
IGNOREHEADER 1
ACCEPTANYDATE
BLANKSASNULL
DATEFORMAT 'auto'
EMPTYASNULL
EXPLICIT_IDS
FILLRECORD
IGNOREBLANKLINES
TRIMBLANKS
REGION AS 'us-west-2'
My question is how can I use the COPY command to load this table and have it pick up the values for these fields as they are in the file but use the defaults when values are not present? I have researched this here in re:Post as well as other forums such as StackOverflow but have not found anything that addresses this specific scenario. Any help would be appreciated.
Thanks Ziad. Not the answer I was hoping for but will try this. I will mark your answer as accepted and close the questions.