COPY command ignoring defaults if some values are present in specific fields

0

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.

asked 2 years ago695 views
1 Answer
0
Accepted Answer

Hi PurpleHaze,

Usually the DEFAULT expression evaluation for a given column is applied the same for all the loaded rows. If you don't specify these fields in the COPY column list, all the rows in the file should have no value for these fields in order for the DEFAULT option to work otherwise the command will fail with the "Extra column(s) found". For the moment, in the above case, copying your data to a staging table and then applying the default values on the NULL while copying/moving the data to the target table is an option.

Thanks,

AWS
EXPERT
Ziad
answered 2 years ago
  • Thanks Ziad. Not the answer I was hoping for but will try this. I will mark your answer as accepted and close the questions.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions