title_wip

In this guide, we'll learn how to add a column to an existing table.


## Adding a Column to a Table[​](#adding-a-column-to-a-table "Direct link to Adding a Column to a Table")

We'll be using clickhouse-local:

```
clickhouse -m --output_format_pretty_row_numbers=
```

Let's imagine we have the following table:

```
CREATE TABLE events (
    date Date DEFAULT today(), 
    name String
) 
ENGINE = MergeTree
ORDER BY date;
```

Let's add one record:

```
INSERT INTO events (name) VALUES ('Alexey');
```

And now query the `events` table:

```
SELECT *
FROM events;
```

```
┌───────date─┬─name───┐
│ 2024-12-18 │ Alexey │
└────────────┴────────┘
```

## Adding a new column[​](#adding-a-new-column "Direct link to 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`](/docs/sql-reference/statements/alter/column#add-column) clause:

```
ALTER TABLE events 
ADD COLUMN favoriteNumber Float64 DEFAULT 7;
```

If we query the `events` table, we'll see the following output:

```
┌───────date─┬─name───┬─favoriteNumber─┐
│ 2024-12-18 │ Alexey │              7 │
└────────────┴────────┴────────────────┘
```

The `Alexey` row defaults to 7 since that column didn't exist when we added that row.
Next, let's add another column:

```
INSERT INTO events (name) VALUES ('Tyler');
```

If we query the `events` table, we'll see the following output:

```
┏━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃       date ┃ name   ┃ favoriteNumber ┃
┡━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ 2024-12-18 │ Tyler  │              7 │
├────────────┼────────┼────────────────┤
│ 2024-12-18 │ Alexey │              7 │
└────────────┴────────┴────────────────┘
```

## Modifying a column's default value[​](#modifying-a-columns-default-value "Direct link to Modifying a column's default value")

If we modify the `favoriteNumber` column to have a different type using the [`ALTER TABLE...MODIFY COLUMN`](/docs/sql-reference/statements/alter/column#modify-column) clause, things get interesting:

```
ALTER TABLE events 
MODIFY COLUMN favoriteNumber Float64 DEFAULT 99;
```

If we query `events` again, we'll see this output:

```
┏━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃       date ┃ name   ┃ favoriteNumber ┃
┡━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ 2024-12-18 │ Tyler  │              7 │
├────────────┼────────┼────────────────┤
│ 2024-12-18 │ Alexey │             99 │
└────────────┴────────┴────────────────┘
```

`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`](/docs/sql-reference/statements/optimize)  to force current defaults to be written to disk:

```
OPTIMIZE TABLE events;
```

Once we've done that, let's say we change the default value again:

```
ALTER TABLE events 
MODIFY COLUMN favoriteNumber Float64 DEFAULT 21;
```

And then insert another row:

```
INSERT INTO events (name) VALUES ('Tanya');
```

Finally, let's query `events` one more time:

```
┏━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃       date ┃ name   ┃ favoriteNumber ┃
┡━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ 2024-12-18 │ Alexey │             99 │
├────────────┼────────┼────────────────┤
│ 2024-12-18 │ Tyler  │              7 │
├────────────┼────────┼────────────────┤
│ 2024-12-18 │ Tanya  │             21 │
└────────────┴────────┴────────────────┘
```

`Tanya` picks up the new default of `21`, but `Alexey` has the old default of `99`.

## Controlling column position in table[​](#controlling-column-position-in-table "Direct link to 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:

```
ALTER TABLE events
ADD COLUMN favoriteColor String DEFAULT 'Yellow' AFTER name;
```

Let's query `events`:

```
┏━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃       date ┃ name   ┃ favoriteColor ┃ favoriteNumber ┃
┡━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ 2024-12-18 │ Alexey │ Yellow        │             99 │
├────────────┼────────┼───────────────┼────────────────┤
│ 2024-12-18 │ Tyler  │ Yellow        │              7 │
├────────────┼────────┼───────────────┼────────────────┤
│ 2024-12-18 │ Tanya  │ Yellow        │             21 │
└────────────┴────────┴───────────────┴────────────────┘
```

And if we wanted to add a column `favoriteDatabase` and have that be first in the list, we could do this:

```
ALTER TABLE events
ADD COLUMN favoriteDatabase String DEFAULT 'ClickHouse' FIRST;
```

```
┏━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ favoriteDatabase ┃       date ┃ name   ┃ favoriteColor ┃ favoriteNumber ┃
┡━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ ClickHouse       │ 2024-12-18 │ Tanya  │ Yellow        │             21 │
├──────────────────┼────────────┼────────┼───────────────┼────────────────┤
│ ClickHouse       │ 2024-12-18 │ Alexey │ Yellow        │             99 │
├──────────────────┼────────────┼────────┼───────────────┼────────────────┤
│ ClickHouse       │ 2024-12-18 │ Tyler  │ Yellow        │              7 │
└──────────────────┴────────────┴────────┴───────────────┴────────────────┘
```

And let's have a look at the table definition:

```
SHOW CREATE TABLE events
FORMAT LineAsString
```

```
CREATE TABLE default.`clickhouse-local-ab404c86-56cc-495b-ad1d-fb343cac3bc0events`
(
    `favoriteDatabase` String DEFAULT 'ClickHouse',
    `date` Date DEFAULT today(),
    `name` String,
    `favoriteColor` String DEFAULT 'Yellow',
    `favoriteNumber` Float64 DEFAULT 21
)
ENGINE = MergeTree
ORDER BY date
```

Dec 18, 2024 · 4 min read