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.

feita há um ano595 visualizações
1 Resposta
0
Resposta aceita

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
ESPECIALISTA
Ziad
respondido há um ano
  • Thanks Ziad. Not the answer I was hoping for but will try this. I will mark your answer as accepted and close the questions.

Você não está conectado. Fazer login para postar uma resposta.

Uma boa resposta responde claramente à pergunta, dá feedback construtivo e incentiva o crescimento profissional de quem perguntou.

Diretrizes para responder a perguntas