Search code examples
singlestore

MemSQL - how to create a column default with the current date?

Getting this error when trying to create a table with a default value for a "_loaded_at" column:

ERROR 1067 (42000): Invalid default value for '_loaded_at'

This does not work:

CREATE TABLE json01(
id BIGINT PRIMARY KEY AUTO_INCREMENT
, _loaded_at DATETIME DEFAULT NOW()
, properties JSON NOT NULL
, SHARD KEY (id)
);

Whereas this does work:

CREATE TABLE json01(
id BIGINT PRIMARY KEY AUTO_INCREMENT
, _loaded_at DATETIME DEFAULT '1970-01-01 00:00:01'
, properties JSON NOT NULL
, SHARD KEY (id)
);

I also tried with the function UTC_TIMESTAMP(). Hoping that there is a way to specify a function as the default, since this is pretty standard functionality. Thanks so much for your help!

Solution

  • How about considering something like: 

    _loaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

    ?