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

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

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

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

ALTER TABLE users
ADD COLUMN email String DEFAULT '';

2. Integer / Float

ALTER TABLE metrics
ADD COLUMN score Float64 DEFAULT 0.0;

3. DateTime

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

4. Array

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

5. Map

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

6. JSON (ClickHouse ≥ 25.3)

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

Supports direct JSON path queries:

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

---

🎛️ Column Order

You can control where the column appears:

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

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:

    OPTIMIZE TABLE table_name;
  

---

✅ Summary

---

💬 ChatGPT Helper Snippet 🤖

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

💡 **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:

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.

---