Redshift: creating a table with Timestamp column defaulting to Now()

Question

Is there a way to create a table with a timestamp column defaulting to now?

Any of the following succeeds on creation but fails on insertion.

CREATE TABLE MyTable ( device_id         VARCHAR(64) NOT NULL encode lzo, app_id            VARCHAR(150) NOT NULL distkey encode lzo, retrieval_date    TIMESTAMP without time zone default (now() at time zone 'utc') encode lzo ) diststyle key compound sortkey(app_id,device_id); 

Then on insert:

     insert into MyTable (device_id,app_id) values('a','b'); INFO:  Function "timezone(text,timestamp with time zone)" not supported. INFO:  Function "now()" not supported. ERROR:  Specified types or functions (one per INFO message) not supported on Redshift tables. 

I tried other flavors as below but all fail.

a) Tried with now in single quotes 'now' , create succeeded but failed with another error

b) Tried without the timezone, create succeeded, insert failed.

Answer 1

You can use SYSDATE or GETDATE() to put a current timestamp value. Here is an example.

dev=> create table my_table (id int, created_at datetime default sysdate); CREATE TABLE  dev=> insert into my_table (id) values (1); INSERT 0 1  dev=> select * from my_table;  id |        created_at ----+---------------------------   1 | 2016-01-04 19:07:14.18745 (1 row) 

• SYSDATE (Oracle Compatibility Function)
  • http://docs.aws.amazon.com/redshift/latest/dg/r_SYSDATE.html
• GETDATE()
  • http://docs.aws.amazon.com/redshift/latest/dg/r_GETDATE.html