Adding a column to a table
Adding a Column to a Table
We'll be using clickhouse-local:
Let's imagine we have the following table:
Let's add one record:
And now query the events table:
Adding a new column
Now let's say we're going to add a new column called favoriteNumber, which will be a Float64.
We can do this using the ALTER TABLE...ADD COLUMN clause:
If we query the events table, we'll see the following output:
The Alexey row defaults to 7 since that column didn't exist when we added that row.
Next, let's add another column:
If we query the events table, we'll see the following output:
Modifying a column's default value
If we modify the favoriteNumber column to have a different type using the ALTER TABLE...MODIFY COLUMN clause, things get interesting:
If we query events again, we'll see this output:
Tyler keeps a value of 7, which was the default when that row was created.
Alexey picks up the new default of 99 because the favoriteNumber column didn't exist when that row was created.
If we want the Alexey row to use the current default right away, we need to call OPTIMIZE TABLE to force current defaults to be written to disk:
Once we've done that, let's say we change the default value again:
And then insert another row:
Finally, let's query events one more time:
Tanya picks up the new default of 21, but Alexey has the old default of 99.
Controlling column position in table
When we add a new column, by default it will be added at the end of the table.
But, we can use the FIRST and AFTER clauses to control where a column is positioned.
For example, if we wanted to add a column called favoriteColor after the name column, we could do this:
Let's query events:
And if we wanted to add a column favoriteDatabase and have that be first in the list, we could do this:
And let's have a look at the table definition: