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.

demandé il y a un an595 vues
1 réponse
0
Réponse acceptée

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
répondu il y a un an
  • Thanks Ziad. Not the answer I was hoping for but will try this. I will mark your answer as accepted and close the questions.

Vous n'êtes pas connecté. Se connecter pour publier une réponse.

Une bonne réponse répond clairement à la question, contient des commentaires constructifs et encourage le développement professionnel de la personne qui pose la question.

Instructions pour répondre aux questions