4

does anyone know how to use a default value like current_timestamp in mysql when creating a clickhouse table? The now() udf is dynamic, instead of the time the row inserted, it is always the current time, it changes when select.

Here is my table:

CREATE TABLE default.test2 (   
`num` UInt32,   
`dt` String,  
`__inserted_time` DateTime DEFAULT now()  
)  
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/test2', '{replica}')  
PARTITION BY dt  
ORDER BY dt  
SETTINGS index_granularity = 8192

I want the __inserted_time column value generated automatically thus I don't have to specify it in the insert into test2 (num,dt) values (1,'20191010')

my mistake, DEFAULT now() actually works

kaijian.ding
  • 41
  • 1
  • 4
  • 1
    "*it is always the current time, it changes when select*" - you are wrong, the default value should be calculated just once when row inserted. – vladimir Oct 23 '19 at 04:04
  • 1
    you are right, it works. I guess there is some mistake when I did the test yesterday. – kaijian.ding Oct 23 '19 at 05:38
  • 2
    if you add column by alter table the column default will be calculated on the fly in select until this part re-merged and this new column be saved on a disk. You can 'materialize' default by alter table x update colx=colx where 1; or by OPTIMIZE – Denny Crane Oct 23 '19 at 13:26

0 Answers0