DUPLICATE_COLUMN ClickHouse ClickHouse error¶

Common cause: You have duplicate column names in your SELECT statement, table definition, or query result.

The DUPLICATE_COLUMN error occurs when you have multiple columns with the same name in your query or table definition. This commonly happens when using aliases, JOINs, or when selecting columns that have the same name from different tables.

What causes this error¶

This error typically happens when:

Duplicate column names: Same column name appears multiple times in SELECT
JOIN conflicts: Columns with same name from different tables
Alias conflicts: Multiple columns with same alias name
Subquery issues: Duplicate column names in subqueries
Table definition: Duplicate column names in CREATE TABLE
UNION conflicts: Same column names in UNION queries
Aggregate functions: Multiple aggregate functions with same alias

Example errors¶

-- Error: Duplicate column 'user_id'
SELECT user_id, user_id FROM events;

-- Error: Duplicate column in JOIN
SELECT e.user_id, u.user_id FROM events e JOIN users u ON e.user_id = u.user_id;

-- Error: Duplicate alias
SELECT user_id AS id, event_type AS id FROM events;

-- Error: Duplicate column in table definition
CREATE TABLE events (
    id UInt32,
    user_id UInt32,
    user_id UInt32
);

-- Error: Duplicate column in UNION
SELECT user_id FROM events UNION SELECT user_id FROM users;

How to fix it¶

1. Use unique column aliases¶

Give duplicate columns unique aliases:

-- ❌ Wrong: Duplicate column names
SELECT user_id, user_id FROM events;

-- ✅ Correct: Unique aliases
SELECT user_id AS user_id_1, user_id AS user_id_2 FROM events;

-- ❌ Wrong: Duplicate aliases
SELECT user_id AS id, event_type AS id FROM events;

-- ✅ Correct: Unique aliases
SELECT user_id AS user_id, event_type AS event_type FROM events;

2. Use table prefixes in JOINs¶

Use table prefixes to distinguish columns from different tables:

-- ❌ Wrong: Ambiguous column names in JOIN
SELECT user_id FROM events e JOIN users u ON e.user_id = u.user_id;

-- ✅ Correct: Use table prefixes
SELECT e.user_id AS event_user_id, u.user_id AS user_user_id 
FROM events e JOIN users u ON e.user_id = u.user_id;

-- ✅ Correct: Select specific columns
SELECT e.user_id, e.event_type, u.name 
FROM events e JOIN users u ON e.user_id = u.user_id;

3. Fix table definitions¶

Ensure unique column names in table definitions:

-- ❌ Wrong: Duplicate column names
CREATE TABLE events (
    id UInt32,
    user_id UInt32,
    user_id UInt32
);

-- ✅ Correct: Unique column names
CREATE TABLE events (
    id UInt32,
    user_id UInt32,
    event_user_id UInt32
);

4. Handle UNION queries¶

Use aliases to distinguish columns in UNION:

-- ❌ Wrong: Duplicate column names in UNION
SELECT user_id FROM events UNION SELECT user_id FROM users;

-- ✅ Correct: Use aliases in UNION
SELECT user_id AS user_id FROM events 
UNION 
SELECT user_id AS user_id FROM users;

Common patterns and solutions¶

JOIN with duplicate column names¶

-- ❌ Wrong: Ambiguous column names
SELECT user_id FROM events e JOIN users u ON e.user_id = u.user_id;

-- ✅ Correct: Use table prefixes
SELECT e.user_id AS event_user_id, u.user_id AS user_user_id 
FROM events e JOIN users u ON e.user_id = u.user_id;

-- ✅ Correct: Select specific columns
SELECT e.user_id, e.event_type, u.name 
FROM events e JOIN users u ON e.user_id = u.user_id;

Subquery with duplicate columns¶

-- ❌ Wrong: Duplicate column in subquery
SELECT user_id FROM (
    SELECT user_id, user_id AS duplicate_user_id FROM events
);

-- ✅ Correct: Unique column names
SELECT user_id FROM (
    SELECT user_id, user_id AS user_id_2 FROM events
);

Aggregate functions with same alias¶

-- ❌ Wrong: Duplicate aggregate aliases
SELECT count() AS total, count() AS total FROM events;

-- ✅ Correct: Unique aggregate aliases
SELECT count() AS total_events, count() AS total_users FROM events;

-- ✅ Correct: Different aggregate functions
SELECT count() AS total_events, uniq(user_id) AS unique_users FROM events;

Advanced solutions¶

Using column aliases systematically¶

-- Use consistent naming for similar columns
SELECT 
    e.user_id AS event_user_id,
    e.timestamp AS event_timestamp,
    u.user_id AS user_user_id,
    u.created_at AS user_created_at
FROM events e 
JOIN users u ON e.user_id = u.user_id;

Handling complex JOINs¶

-- Multiple table JOIN with clear column naming
SELECT 
    e.id AS event_id,
    e.user_id AS event_user_id,
    e.timestamp AS event_timestamp,
    u.id AS user_id,
    u.name AS user_name,
    s.id AS session_id,
    s.started_at AS session_started_at
FROM events e
JOIN users u ON e.user_id = u.id
JOIN sessions s ON e.session_id = s.id;

Dynamic column selection¶

-- Avoid duplicate columns in dynamic queries
SELECT 
    user_id,
    event_type,
    timestamp,
    CASE 
        WHEN event_type = 'click' THEN 'click_event'
        WHEN event_type = 'view' THEN 'view_event'
        ELSE 'other_event'
    END AS event_category
FROM events;

Tinybird-specific notes¶

In Tinybird, this error commonly occurs when:

Data source definitions: Duplicate column names in schema
Pipe transformations: Duplicate column names in pipe SQL
JOIN operations: Ambiguous column names in joins
Aggregate queries: Multiple aggregate functions with same alias

Debugging in Tinybird¶

Check data source schema: Verify no duplicate column names
Review pipe SQL: Ensure unique column names in transformations
Test with tb sql: Use CLI to test column naming
Validate JOIN operations: Check column prefixes in joins
Use Query Builder: Test column naming interactively

Common Tinybird scenarios¶

-- In data source definitions, ensure unique column names
SCHEMA >
    `timestamp` DateTime,
    `user_id` UInt32,
    `event_type` String,
    `data` String

-- ❌ Wrong: Duplicate column names in pipe
NODE processed_data
SQL >
    SELECT 
        user_id,
        user_id AS duplicate_user_id
    FROM your_data_source;

-- ✅ Correct: Unique column names in pipe
NODE processed_data
SQL >
    SELECT 
        user_id,
        event_type
    FROM your_data_source;

JOIN operations in pipes¶

-- In pipe definitions, use clear column naming
NODE joined_data
SQL >
    SELECT 
        e.user_id AS event_user_id,
        e.event_type,
        e.timestamp,
        u.user_id AS user_user_id,
        u.name AS user_name
    FROM your_data_source e
    JOIN users u ON e.user_id = u.user_id;

Best practices¶

Always use unique column names in your queries
Use table prefixes in JOIN operations to avoid ambiguity
Give meaningful aliases to distinguish similar columns
Check for duplicate columns before running complex queries
Use consistent naming conventions across your queries
Test column naming with simple queries first
Document column naming for your team
Use Query Builder to test column naming interactively

ClickHouse errors

Function-specific issues

Performance issues

Schema and typing

Data ingestion