WITH customer AS
(SELECT 'John' AS name,'James' AS name)
SELECT name FROM customer;
returns ERROR: column reference "name" is ambiguous
I can improve into
WITH customer(name1,name2) AS
(SELECT 'John' AS name,'James' AS name)
SELECT name2 FROM customer;
that returns name2 James.
Looking at https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190766(v=sql.105)?redirectedfrom=MSDN, I see.
The basic syntax structure for a CTE is:
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
Well it looks like SQL Server is doing a better job.
I am OK with SELECT 'John' AS name,'James' AS name returning 2 columns with the same name.
I am not OK with the same query used as a sub-select.
On 30/07/2025 23:34, Tom Lane wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
SELECT 'John' AS nom,'James' AS nom
returns a table with attributes nom and nom that I can see in the output.
Returning a table that contains twice the same column should not be
permitted.
There is pretty much zero chance that we will enforce that
restriction. It would break too much application code.
Also, it looks to me like there is no such requirement in
the SQL standard.
Indeed. In SQL:2023-1 (available free of charge at [1]) says in Subclause 4.6, "Tables":
"An operation that references zero or more base tables and returns a table is called a *query*. The result of a query is called a *derived table*."
and
"Derived tables, other than viewed tables, may contain more than one column with the same name."
So, not only is this not a bug we should fix, it is explicitly allowed by the standard.
[1] https://www.iso.org/standard/76583.html
--
Vik Fearing
