ClickHouse DB::Exception: Duplicate column name

The "DB::Exception: Duplicate column" error in ClickHouse means that two or more columns in a result set, table definition, or intermediate block share the same name. ClickHouse requires column names to be unique within a single block, so any operation that produces duplicates — whether it's a CREATE TABLE, a SELECT with ambiguous aliases, or a JOIN — will be rejected.

Impact

This error prevents the query or DDL statement from executing. In production it commonly breaks JOIN-heavy analytics queries and can block table creation scripts during migrations. If it happens inside a materialized view definition, every INSERT to the source table will fail until the MV is fixed.

Common Causes

1. JOIN without qualified column names — when both sides of a JOIN have a column with the same name and you select both without aliases, the result block contains duplicates.
2. SELECT * with a JOIN — SELECT * pulls in all columns from all joined tables, including those that share names.
3. CREATE TABLE with repeated column names — a typo or copy-paste error in DDL that lists the same column twice.
4. Subqueries or CTEs producing duplicate names — two computed expressions given identical aliases.
5. ARRAY JOIN or CROSS JOIN expanding columns — certain join patterns can inadvertently produce duplicate names in the output.

Troubleshooting and Resolution Steps

1. Locate the duplicate. The error message includes the offending column name. Search your query for every occurrence of that name.

2. Alias conflicting columns in JOINs. When two tables share a column name, alias at least one of them:

   SELECT
       a.id AS a_id,
       b.id AS b_id,
       a.value,
       b.value AS b_value
   FROM table_a AS a
   JOIN table_b AS b ON a.id = b.id;
   

3. Replace SELECT * with explicit column lists. This is the most reliable way to avoid accidental duplicates from joins:

   -- Instead of SELECT *
   SELECT a.id, a.name, b.status
   FROM orders AS a
   JOIN customers AS b ON a.customer_id = b.id;
   

4. Fix CREATE TABLE definitions. If the error occurs at table creation time, review the DDL for repeated column names:

   -- Wrong: duplicate "ts"
   CREATE TABLE events (
       id UInt64,
       ts DateTime,
       ts DateTime  -- duplicate!
   ) ENGINE = MergeTree() ORDER BY id;
   

5. Use USING instead of ON for natural key joins. The USING clause automatically deduplicates the join key column:

   SELECT *
   FROM table_a
   JOIN table_b USING (id);
   

6. Check materialized view SELECT clauses. If an MV triggers the error, inspect its definition with SHOW CREATE TABLE and add aliases to resolve ambiguities.

Best Practices

• Avoid SELECT * in production queries, especially those involving joins — list only the columns you need.
• Always alias columns in multi-table queries to make the origin of each column clear.
• Use USING for equi-joins on identically named keys to avoid manual deduplication.
• Review CREATE TABLE scripts with a linter or diff tool before deploying schema changes.

Frequently Asked Questions

Q: Why does ClickHouse raise DUPLICATE_COLUMN when my SQL database doesn't?
A: Some databases silently rename duplicates (e.g., appending _1) or allow ambiguous result sets. ClickHouse enforces uniqueness strictly because its columnar engine requires unambiguous column references in every block.

Q: Can I use SELECT * safely with USING joins?
A: Yes. When you use USING (col), ClickHouse includes the join key only once in the result, which avoids the duplicate. With ON, both copies are kept.

Q: I get this error in a UNION ALL. How do I fix it?
A: UNION ALL itself doesn't produce duplicates across branches, but if one branch internally has duplicate column names the error propagates. Fix the individual SELECT that contains the duplicate.

Q: Does renaming a column with AS always resolve the issue?
A: Yes, as long as the alias you choose is unique within that query level. Make sure two different columns don't accidentally share the same alias.