CODEWITHSUNDEEP
clickhouseclickhouse-client
Marios Ath
Marios Ath

Reputation: 638

How to ALTER TABLE [X] ADD COLUMN [Y] [type] NOT NULL in clickhouse?

When creating a table in Clickhouse DB you can do :

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

For adding a new column with ALTER

ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after | FIRST]

It seems to not be working when for example I do

ALTER TABLE product_stock_history_analytics
                ADD COLUMN IF NOT EXISTS product_id STRING NOT NULL;

Is there a way to set a new column to NOT NULL in a pre-existing table? Do I have to set a default value in order to achieve the desired effect ?

Upvotes: 1

Views: 1365

Answers (1)

Geoff Genz
Geoff Genz

Reputation: 2119

ClickHouse columns are not Nullable by default. At the moment the standard SQL NOT NULL is not supported in ALTER TABLE statements, but it's also not necessary.

ALTER TABLE product_stock_history_analytics ADD COLUMN IF NOT EXISTS product_id String;

will create a not nullable String column using the standard (non ANSI SQL) ClickHouse syntax.

Upvotes: 1

Related Questions