Clickhouse: Question: How to Modify Column Type (String->Float64) with Empty Value ?

[AlexanderJLiu]

I've tried to modify column with empty value from String to Float64 using command:
alter table name modify column col Float64
And it gives the exception:
DB::Exception: Cannot read floating point value: Cannot parse Float64 from String
There are some empty value '' in that column, which lead to the exception as shown above.
I've also tried to use default expersion:
alter table name modify column col Float64 default 0
But it didn't work.
What should I do to solve this problem ?

PS: There are too many data in table and it isn't convenient to rename a new table using insert into * select

Thanks for your advice. 👍

[filimonov]

Dropping the column and adding the new one with the same name should work.

[alesapin]

You can use new ALTER UPDATE feature. Example:

:) CREATE TABLE default.some_table ( client_id Int32,  some_column String) ENGINE = MergeTree ORDER BY tuple() SETTINGS index_granularity = 8192

:) INSERT INTO some_table VALUES (1, '1.0'), (2, '3.6'), (3, ''), (5, '172');     

:) SELECT * FROM some_table;

┌─client_id─┬─some_column─┐
│         1 │ 1.0         │
│         2 │ 3.6         │
│         3 │             │
│         5 │ 172         │
└───────────┴─────────────┘
:) ALTER TABLE some_table UPDATE some_column='0.0' WHERE some_column='';                                                                                                                                                                                                                

Ok.

-- wait until mutation complete

:) SELECT command, is_done FROM system.mutations;                                                                                                                                                                                                                                                                                                       

SELECT 
    command, 
    is_done
FROM system.mutations 

┌─command───────────────────────────────────────────────────────────────┬─is_done─┐
│ UPDATE some_column = '0.0' WHERE some_column = ''                     │       1 │
└───────────────────────────────────────────────────────────────────────┴─────────┘

:) SELECT * FROM some_table;                                                                                                                                                                                                                                                                                                                

┌─client_id─┬─some_column─┐
│         1 │ 1.0         │
│         2 │ 3.6         │
│         3 │ 0.0         │
│         5 │ 172         │
└───────────┴─────────────┘

-- wait about 10 minutes until old parts will be removed

:) ALTER TABLE some_table MODIFY COLUMN some_column Float64;

ALTER TABLE some_table
    MODIFY COLUMN 
    some_column Float64

Ok.

0 rows in set. Elapsed: 0.005 sec.

[AlexanderJLiu]

Dropping the column and adding the new one with the same name should work.

@filimonov Thanks，but the data already existed would be lost. This would be the last option I choose. 😭

[AlexanderJLiu]

@alesapin This is awsome, but the clickhouse version doesn't support update.

[alesapin]

It was introduced in 18.12.14. You have to update, current stable is 18.14.11.

[filimonov]

Dropping the column and adding the new one with the same name should work.

@filimonov Thanks，but the data already existed would be lost. This would be the last option I choose.

OK. I've misunderstood your first sentence: 'modify column with empty value'.

On older versions you can try some workarounds like create new column with default expression, make OPTIMIZE to materialize new column, and after that drop old column.