# How to Add a New Column in ClickHouse

Adding a new column in ClickHouse is a common task when evolving your schema. Whether it’s a metric, metadata, or a JSON field, ClickHouse supports flexible and efficient schema changes — even at scale.

---

## ⚙️ Basic Syntax

```sql
ALTER TABLE table_name
ADD COLUMN column_name ColumnType [DEFAULT expression] [AFTER existing_column | FIRST];
```

**Parameters**

* `table_name` → your existing table name
    
* `column_name` → the new column name
    
* `ColumnType` → column data type (String, UInt32, DateTime, JSON, etc.)
    
* `DEFAULT expression` → sets the default value for existing and future rows
    
* `AFTER` / `FIRST` → controls where the new column appears
    

---

## 🧩 Example

```sql
ALTER TABLE events
ADD COLUMN city String DEFAULT 'Unknown';
```

✅ Adds a `city` column  
✅ All existing rows are filled with `'Unknown'`  
✅ Future rows get `'Unknown'` if the column isn’t specified

---

## 🧠 Why `DEFAULT` Matters

When you add a new column, ClickHouse needs a value for old rows that already exist in storage.

* **Without a** `DEFAULT`: ClickHouse may throw an error or use `NULL` (engine-dependent)
    
* **With a** `DEFAULT`: Old rows get a defined value, ensuring queries stay consistent
    

```sql
ALTER TABLE users
ADD COLUMN age UInt8 DEFAULT 0;
```

👉 All existing users now have `age = 0`.  
👉 New inserts without `age` also default to `0`.

✅ **Best Practice:** Always define a default — it prevents nulls and ensures safe queries.

---

## 📊 Common Column Types

### 1\. String

```sql
ALTER TABLE users
ADD COLUMN email String DEFAULT '';
```

### 2\. Integer / Float

```sql
ALTER TABLE metrics
ADD COLUMN score Float64 DEFAULT 0.0;
```

### 3\. DateTime

```sql
ALTER TABLE logs
ADD COLUMN created_at DateTime DEFAULT now();
```

### 4\. Array

```sql
ALTER TABLE products
ADD COLUMN tags Array(String) DEFAULT [];
```

### 5\. Map

```sql
ALTER TABLE settings
ADD COLUMN properties Map(String, String) DEFAULT {};
```

### 6\. JSON (ClickHouse ≥ 25.3)

```sql
ALTER TABLE events
ADD COLUMN metadata JSON DEFAULT '{}';
```

Supports direct JSON path queries:

```sql
SELECT metadata.user.id, metadata.device.model FROM events;
```

---

## 🎛️ Column Order

You can control where the column appears:

```sql
ALTER TABLE events
ADD COLUMN color String DEFAULT 'Blue' AFTER name;

ALTER TABLE events
ADD COLUMN source String DEFAULT 'web' FIRST;
```

This only affects schema order (not physical storage).

---

## 🔄 Modify or Drop Columns

```sql
ALTER TABLE events
MODIFY COLUMN color String DEFAULT 'Red';

ALTER TABLE events
DROP COLUMN color;
```

---

## ⚡ Under the Hood

* ClickHouse **does not rewrite old data immediately**.
    
* Default values are applied **on read**, not on disk.
    
* To persist defaults physically:
    
    ```sql
    OPTIMIZE TABLE table_name;
    ```
    

---

## ✅ Summary

| Concept | Description |
| --- | --- |
| **ADD COLUMN** | Adds a new field |
| **DEFAULT** | Fills old + missing rows |
| **Column Order** | Controlled by `FIRST` / `AFTER` |
| **Common Types** | String, Number, DateTime, Array, Map, JSON |
| **Best Practice** | Always define a default |
| **Version Tip** | Use native `JSON` in ClickHouse ≥ 25.3 |

---

## 💬 ChatGPT Helper Snippet 🤖

If you’re unsure what SQL to use, just paste this into ChatGPT:

```markdown
💡 **Prompt for ChatGPT:**

Generate a ClickHouse `ALTER TABLE` query to add a new column.

Here are my details:
- Table name: `<your_table_name>`
- Column name: `<new_column_name>`
- Data type: `<type>` (e.g., String, UInt8, Float64, JSON, Array(String), etc.)
- Default value: `<default_value>` (e.g., '{}', 0, '', now())

✅ Requirements:
1. Include the correct syntax for ClickHouse.
2. Add a short explanation of what the command does.
3. If JSON type, ensure it's compatible with ClickHouse ≥ 25.3.
```

Example usage in ChatGPT:

> “Generate a ClickHouse query to add a column `label` of type JSON with default `{}` to my table `ad_metadata_v3`.”

👉 ChatGPT will output:

```sql
ALTER TABLE ad_metadata_v3
ADD COLUMN label JSON DEFAULT '{}';
```

---

### 🚀 Final Takeaway

* Adding columns in ClickHouse is safe and efficient.
    
* Always define a `DEFAULT` for stability.
    
* Use `JSON`, `Map`, or `Array` for flexible, semi-structured data.
    
* For automation or documentation, use the ChatGPT helper snippet above for fast SQL generation.
    

---