Auto incremental

[James Wang]

Hi All,

Does CH support AUTO_INCREMENT as in MySQL please?

If yes, how to define one?

If not, any similar concept please?

Thanks a lot in advance

[Николай Кочетов]

Hi,

Now ClickHouse doesn't support AUTO_INCREMENT. Now you only can support it manually, outside of ClickHouse.

вторник, 26 сентября 2017 г., 17:08:09 UTC+3 пользователь James Wang написал:

[James Wang]

How to manually please?  It would be great if you could shed some light - prefer a sample.

Thank a lot in advance indeed.

[James Wang]

I had tried insertID() without success:

create table test.c4 (id UInt64 insertID(),date Date DEFAULT toDate(now())) ENGINE = MergeTree(date, (id, date), 8192);

please help.

thanks a lot in advance

[James Wang]

Is this the correct way to do this?

insert into test.c4 select id+1,toDate(now()) from test.c4;

Any implications?

Thanks a lot

[Николай Кочетов]

Probably, you can do something like:

create table test4 (date Date, val UInt64) engine = MergeTree(date, (date), 8192);
insert into test4 values (today(), 0);
select * from test4;

┌───────date─┬─val─┐
│ 2017-09-27 │   0 │
└────────────┴─────┘

insert into test4 select today(), (select count(*) from test4) + number from system.numbers limit 5;
select * from test4;
┌───────date─┬─val─┐
│ 2017-09-27 │   0 │
└────────────┴─────┘
┌───────date─┬─val─┐
│ 2017-09-27 │   1 │
│ 2017-09-27 │   2 │
│ 2017-09-27 │   3 │
│ 2017-09-27 │   4 │
│ 2017-09-27 │   5 │
└────────────┴─────┘

insert into test4 select today(), (select count(*) from test4) + number from system.numbers limit 5;
select * from test4;
┌───────date─┬─val─┐
│ 2017-09-27 │   1 │
│ 2017-09-27 │   2 │
│ 2017-09-27 │   3 │
│ 2017-09-27 │   4 │
│ 2017-09-27 │   5 │
└────────────┴─────┘
┌───────date─┬─val─┐
│ 2017-09-27 │   6 │
│ 2017-09-27 │   7 │
│ 2017-09-27 │   8 │
│ 2017-09-27 │   9 │
│ 2017-09-27 │  10 │
└────────────┴─────┘
┌───────date─┬─val─┐
│ 2017-09-27 │   0 │
└────────────┴─────┘

But, this approach isn't good enough. For example, if one insert starts when other isn't finished, count(*) will return old value, and ids will overlap.
Do you really need AUTO_INCREMENT? Date or DateTime value may be enough to distinguish rows.

среда, 27 сентября 2017 г., 14:23:55 UTC+3 пользователь James Wang написал:

[James Wang]

Thanks a lot indeed.

Yes I need a unique ID which is a lot easier to distinguish a row from others than date/datetime :)

Thanks

[Itai Shirav]

Consider using a UUID as the unique id of each row. You can easily generate a UUID in any programming language when inserting each row, and then encode it to FixedString(16) using the UUIDStringToNum function in ClickHouse.

[James Wang]

Thanks a lot indeed.

Shall give it a try.