AWS Redshift -- loading into a TIMESTAMP field

Question

I'm using an AWS Kinesis Firehose to write a file to S3, which in turn loads into Redshift. My table has a TIMESTAMP column on it:

create table TABLE_NAME
(
  ...
  COLUMN_NAME TIMESTAMP,
  ...
)

The Redshift COPY options in the Firehose give the format for the column:

DELIMITER '\t' GZIP DATEFORMAT AS 'YYYYMMDD' TIMEFORMAT AS 'YYYYMMDDHH24MISS' MAXERROR 1000

The data for COLUMN_NAME isn't always populated. In that case, I set the data for the TIMESTAMP column to an empty string. When this happens, I'm seeing Redshift load errors with error code 1206:

Invalid timestamp format or value [YYYYMMDDHH24MISS]

The interesting part is that my load is still succeeding. The records with an empty string for this TIMESTAMP column still load to my Redshift table just fine, but I keep seeing these errors logged to the stl_load_errors table.

Is there a Redshift COPY option I should be setting to ignore these errors? Is there a keyword I should set instead of a blank string in the data?

Answer 1

I think you can use ACCEPTANYDATE param at your COPY command