How to have auto increment in ClickHouse?

Question

I want a column to have a unique value in every insertion. In SQL we can have this using autoincrement, in Clickhouse can we have this functionality using any type like auto increment or any other? I am new to Clickhouse so there may be a terminology mistake.

Amando

There are no foreign keys in Clickhouse. It is not a traditional relational database. – Parturifacient

Answer 1

There is no server-provided auto-increment in ClickHouse.

As stated by other answers, UUID's is the way to go.

Instead, use generateUUIDv4() (see documentation here)

Sample output

SELECT generateUUIDv4();

Use during insert

INSERT INTO t VALUES (generateUUIDv4(), ...);

Hexyl

Answer 2

There's nothing like auto increment in ClickHouse.

If you need unique value, use UUID. It works much better for distributed systems than just auto incremented value

So you can simply generate random Uint64 and convert it to UUID

SELECT toUUID(rand64());

With insert it would look similar to this

INSERT INTO t VALUES (toUUID(rand64()), ...);

Erastatus

it is not working. Error gives: DB::Exception: Element of set in IN or VALUES is not a constant expression: toUUID – Fontanel

@Fontanel just tested and works. Provide more info so I can help. – Erastatus

might be in new versions though – Fontanel

Tested in latest version to Oct 2018 (not sure what it was) and in v20.12 now – Erastatus