CODEWITHSUNDEEP
amazon-redshift
Bruce
Bruce

Reputation: 3518

How to get redshift to add current time for a field specified in copy command

I have a TSV file that I want to load into redshift via the copy command.

I want one of the fields in the table to be a timestamp that registers the time the row was loaded.

I have defined a field like this:

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

This works fine if I insert into this row at the psql command line, without specifying a value for this column - it defaults to the current timestamp as expected.

However, what can I have in my TSV file in that column that will cause redshift to default to the current timestamp?

If I use \N in my TSV, then I just get a NULL in the ts field.

On the other hand, if I define my column as NOT NULL

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL

then I get an error from the COPY command that I can't insert NULL values into a NOT NULL field.

On mysql, mysql would convert a NULL value into the current timestamp, but redshift's behaviour is to throw an error.

Any suggestions? Many thanks!

Upvotes: 1

Views: 1307

Answers (2)

Narendra
Narendra

Reputation: 1

Directly with Copy command we cannot upload the table. There are two way to do the same:

  1. Load the data in a temp table (if using any AWS service) and then update the target table.

    "INSERT INTO schema.target_table (list of all the columns apart from current_date) SELECT all * FROM temp_table;" 2.With Copy Command select all the column apart from "current_date" and execute the statement.

Note: If don't want to list all the column name manually then from information_schema.columns you can list all the column name by providing the table_schema and table_name.

Upvotes: 0

AlexYes
AlexYes

Reputation: 4208

I've been banging my head over this for a while and found one partial workaround: you can have ts column as the last column of your table and the TSV file with all the other columns but this one. The file will be read with the columns that exist and loaded into the consecutive list of columns with the same width in the target table, leaving all columns beyond that width with default values, i.e. you can have id | ts table and load the file with id only and ts will take the default. The current timestamp column is typically a metadata column, so it's ok to place it at the end of the table.

Upvotes: 0

Related Questions