# Duplicate column names in the result are not supported > A plain query may return two columns with the same name and BigQuery renames the second one. A table, a view or a query with a destination table cannot. Each place has its own message, and the case of the name does not matter. Source: https://docs.querylab.io/bigquery-errors/duplicate-column-names-in-the-result-are-not-supported/ Updated: 2026-11-11 ```text Duplicate column names in the result are not supported when a destination table is present. Found duplicate(s): x ``` A plain query does not fail with this message any more. BigQuery renames the second column, as shown below. The message appears when the result is written to a destination table. ## What BigQuery means A table needs a unique name for each column, and BigQuery ignores case when it compares them. When a result is saved, two columns with the same name are refused. The text depends on how the result is saved, and each one is in the table further down. A result that is only shown is not saved, so BigQuery renames the second column: ```sql SELECT 1 AS x, 2 AS x ``` The columns come back as `x` and `x_1`. `SELECT 1 AS x, 2 AS X` gives `x` and `X_1`. ## The smallest case that reproduces it ```sql CREATE TABLE mydataset.t AS SELECT 1 AS x, 2 AS x ``` ```text CREATE TABLE has columns with duplicate name x at [1:29] ``` Name the columns: ```sql CREATE TABLE mydataset.t AS SELECT 1 AS x, 2 AS y ``` ## The message variants | What you ran | Message | | ------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------- | | a query with a destination table, `SELECT 1 AS x, 2 AS x` | `Duplicate column names in the result are not supported when a destination table is present. Found duplicate(s): x` | | the same, with `SELECT 1 AS x, 2 AS X` | `Duplicate column names in the result are not supported when a destination table is present. Found duplicate(s): X` | | a destination table, `SELECT * FROM (SELECT 1 AS x) a, (SELECT 2 AS x) b` | `Duplicate column names in the result are not supported when a destination table is present. Found duplicate(s): x` | | `CREATE TABLE mydataset.t AS SELECT 1 AS x, 2 AS x` | `CREATE TABLE has columns with duplicate name x at [1:29]` | | `CREATE TABLE mydataset.t AS SELECT 1 AS x, 2 AS X` | `CREATE TABLE has columns with duplicate name X at [1:29]` | | `CREATE TABLE mydataset.t AS SELECT x, x FROM (SELECT 1 AS x)` | `CREATE TABLE has columns with duplicate name x at [1:29]` | | `CREATE VIEW mydataset.v AS SELECT 1 AS x, 2 AS x` | `CREATE VIEW has columns with duplicate name x at [1:28]` | | `CREATE TABLE mydataset.t (x INT64, x STRING)` | `Duplicate column name x in CREATE TABLE at [1:36]` | | `CREATE TABLE mydataset.t (x INT64, X STRING)` | `Duplicate column name X in CREATE TABLE at [1:36]` | | `CREATE TABLE mydataset.t (a INT64, a INT64) AS SELECT 1 AS a, 2 AS b` | `Duplicate column name a in CREATE TABLE AS SELECT at [1:36]` | | `CREATE TABLE mydataset.t (a INT64, s STRUCT)` | `Duplicate field name = x` | | `CREATE TABLE mydataset.t AS SELECT STRUCT(1 AS x, 2 AS X) AS s` | `Field s.X already exists in schema` | | `ALTER TABLE mydataset.base ADD COLUMN a INT64`, when `a` exists | `Column already exists: a at [1:39]` | | the same with `ADD COLUMN A INT64` | `Column already exists: A at [1:39]` | | ``CREATE TABLE mydataset.t AS SELECT 1 AS `a b`, 2 AS `a b` `` | ``CREATE TABLE has columns with duplicate name `a b` at [1:29]`` | | ``CREATE TABLE mydataset.`my col` (`a b` INT64, `a b` STRING)`` | `Duplicate column name a b in CREATE TABLE at [1:47]` | | `ALTER TABLE mydataset.two RENAME COLUMN a TO b`, when `b` exists | `Column already exists: b at [1:46]` | | `INSERT INTO mydataset.base (a, a) VALUES (1, 2)` | `INSERT has columns with duplicate name: a at [1:32]` | | `bq mk --table mydataset.t x:INT64,x:STRING` | `Field x already exists in schema` | | `bq mk --table mydataset.t x:INT64,X:STRING` | `Field X already exists in schema` | | `SELECT x FROM (SELECT 1 AS x, 2 AS x)` | `Column name x is ambiguous at [1:8]` | `ALTER TABLE ... ADD COLUMN IF NOT EXISTS a INT64` skips a column that exists and raises no error. The positions shown are for a dataset called `mydataset`. They move with the length of your names. When the name is spelled in two cases, the message names the second spelling. The last row is a different error. The inner query returns two columns named `x`, and the outer query cannot choose one. See [Column name X is ambiguous](https://docs.querylab.io/bigquery-errors/column-name-is-ambiguous/). The same message appears for `SELECT * FROM (SELECT 1 AS x) a CROSS JOIN (SELECT 2 AS x) b ORDER BY x`. ## Where the duplicates come from **`SELECT *` over a join.** Both sides contribute their columns, so any shared name is a duplicate. A plain `SELECT` shows `id`, `v`, `id_1`, `v_1`. Saved as a table it fails: ```sql CREATE TABLE mydataset.t AS WITH a AS (SELECT 1 AS id, 'a' AS v), b AS (SELECT 1 AS id, 'b' AS v) SELECT * FROM a JOIN b ON a.id = b.id ``` ```text CREATE TABLE has columns with duplicate name id at [2:1] ``` `JOIN ... USING (id)` merges the key, and the other shared column is still a duplicate. The same query with `USING (id)` fails with `duplicate name v`. **The same column twice.** `SELECT x, x FROM t` is allowed in a plain query. It returns `x` and `x_1`. Saved, it fails. **Names that differ only in case.** `x` and `X` are the same column to BigQuery. A table cannot have both, whether you write them in a column list, a `SELECT`, an `ALTER TABLE` or a `bq mk` schema. ## Fixes Give each column its own name. For a join, qualify the columns and alias the ones that clash: ```sql CREATE TABLE mydataset.t AS WITH a AS (SELECT 1 AS id, 'a' AS v), b AS (SELECT 1 AS id, 'b' AS v) SELECT a.id, a.v AS a_v, b.v AS b_v FROM a JOIN b ON a.id = b.id ``` This created the table with the columns `id`, `a_v` and `b_v`. When one side has the key you do not need, leave it out with `EXCEPT`: ```sql WITH a AS (SELECT 1 AS id, 'a' AS v), b AS (SELECT 1 AS id, 'b' AS v) SELECT a.* EXCEPT (id), b.v AS b_v FROM a JOIN b ON a.id = b.id ``` This returns `v` and `b_v`. ## Queries that look wrong but run **A plain `SELECT` with two columns of the same name.** The result has the second column renamed. A dry run passes too. Code that reads the result by name sees `x_1`. **A view that fails later.** `bq mk --use_legacy_sql=false --view 'SELECT 1 AS x, 2 AS x' mydataset.v` created the view. The error came when the view was read: ```text Duplicate column name 'x' in definition of view 'mydataset.v' ``` `CREATE VIEW` as a statement refuses it at once. **Legacy SQL.** `bq query --use_legacy_sql 'SELECT 1 AS x, 2 AS x'` fails for a plain query, with `2.17 - 2.22: Ambiguous column name x.`. `bq mk --view` without `--use_legacy_sql=false` gave the same text. ## In Querylab.io For a plain `SELECT` with a repeated alias the editor shows warnings, because BigQuery accepts it and renames the column. `Duplicate column alias 'x'. BigQuery will auto-rename to 'x_1'` is one of them. It shows three warnings for the one problem, so expect to see them together. For `CREATE TABLE ... AS SELECT` the editor marks an error, `CREATE TABLE has columns with duplicate name x`, when the names are written out. The mark covers the `SELECT` keyword and not the columns. It also marks a repeated name in a `CREATE TABLE` column list and in a `STRUCT<...>` type. It does not mark `INSERT INTO t (a, a)`. It does not mark `CREATE VIEW ... AS SELECT 1 AS x, 2 AS x`. It gives the warnings of a plain query, and BigQuery's dry run reports the error. It does not mark `SELECT *` over a join in `CREATE TABLE ... AS`, only the warning `SELECT * with multiple tables may produce ambiguous or duplicate columns`, and it does not mark names that differ in case inside a `STRUCT(...)` that is saved. There is no fix button, because the names are yours to choose. See [Column aliases](https://docs.querylab.io/bigquery/column-aliases/) and [Joins](https://docs.querylab.io/bigquery/joins/). ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Query syntax, Duplicate aliases](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#duplicate_aliases) - [Data definition language, CREATE TABLE](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_statement) - [Writing query results](https://cloud.google.com/bigquery/docs/writing-results)