Redshift: creating a table with Timestamp column defaulting to Now()
<p>Is there a way to create a table with a timestamp column defaulting to now?</p>

<p>Any of the following succeeds on creation but fails on insertion.</p>

<pre><code>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);
</code></pre>

<p>Then on insert:</p>

<pre><code>     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.
</code></pre>

<p>I tried other flavors as below but all fail.</p>

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

<p>b) Tried without the timezone, create succeeded, insert failed.</p>