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.
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:
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
CREATE TABLE mydataset.t AS SELECT 1 AS x, 2 AS x
CREATE TABLE has columns with duplicate name x at [1:29]
Name the columns:
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<x INT64, x INT64>) | 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. 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:
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
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:
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:
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:
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 and Joins.