Duplicate/Ambiguous Column Names

You may have encountered an error such as “The query generated by this panel references an ambiguous column name” or “The saved query joins tables with duplicate column names (and may not work properly with the dashboard).”

Frequently, this occurs when the query generating the dataset selects all columns from the result of a JOIN operation, where the tables JOINed together share column names. We deduplicate those column names for you, adding a number to the end of the duplicate. For example, a query could look like:

SELECT * from table_one t1
JOIN table_two t2
ON t1.column_name = t2.column_name

While this dataset alone does not put your dashboard in a bad state, creating a data panel that leverages one of these duplicated columns will cause an error.

​

How do I resolve this?

​

Explicitly Select Column Names

Instead of using SELECT * to generate columns for your dataset, you could rewrite the query to explicitly name the columns you want to read from. This may lengthen the query you’re writing, but is generally a best practice that avoids pulling in unwanted columns.

SELECT t1.column_name, other_column_name from table_one t1
JOIN table_two t2
ON t1.column_name = t2.column_name

If there are too many columns that you need to pull in, you can also still leverage SELECT *, but either select all columns from a single table or additionally alias your duplicated column to avoid ambiguous references. Note that if you alias the column name, the duplicated columns will still show up in your dataset and should not be used.

# select all columns from one table and some from the other
SELECT t1.\*, t2.other_column_name from table_one t1
JOIN table_two t2
ON t1.column_name = t2.column_name

# select all columns and alias duplicated column
SELECT \*, t1.column_name AS duplicate_column_name from table_one t1
JOIN table_two t2
ON t1.column_name = t2.column_name

​

Use USING Instead of JOIN

Many SQL syntaxes allow for USING statements, which are shorthand for a JOIN on a column name that exists on both tables. This will only generate one column for the duplicated column, but may still generate warnings if your tables have other columns with identical names. Therefore, the USING solution will only work if you’re only using the duplicated column that the query is JOINing on.

SELECT * from table_one t1
JOIN table_two t2
USING (column_name)