Select default value for missing columns

229 views
Skip to first unread message

Sébastien Rebecchi

unread,
Apr 20, 2022, 7:36:03 AM4/20/22
to ClickHouse
Hi everyone!

I am using a table schema with a varying number of columns and where columns are created once firstly needed. So when I insert data that if I got an error of missing column then I create that column and then I retry the insert.

All is OK at insert that is the expected behaviour.

But for select queries, I would like to not having to create a column when the select fails for a missing column. In order not to create "useless" columns, i.e. columns whose values that are never inserted.

What would be ideal is a kind of "select (columnA or 'defaultValueForA'), (columnB or 'defaultValueForB')". Such that if columnA is mising then CH will consider that it is the same as if this column would exist but all rows having defaultValueForA as value. And then CH will process that query successfully instead of throwing error for missing column.
And of course the same is needed for any group by clauses. For ex select .... group by (columnA or 'defaultValueForA'). Which would mean that if columnA does not exist then all rows would be grouped into a single group with value defaultValueForA.

Do you know if this kind of behaviour could be reproduced currently with some complex tricks? And is there a plan in the future to enable that kind of stuff, which would be extremely usefull in use cases where the columns are constantly evolving in an unpredictable way.

Regards,

Sébastien.

Reply all
Reply to author
Forward
0 new messages