I have a query where I want to override one of the output column names. The
problem is that the columns are coming from a subquery. So I have do something
like:
select *, coalesce(a,b) as a from <subquery>
The problem is that * still includes column a. And replacing * with a complete
list of every column coming from the subquery is a non-starter. That would
make maintaining the query a total nightmare. Every change to the subquery
would require editing multiple levels of these explicit lists.
I thought Postgres already allowed for this by taking only the last column by
a given name. At least that's what I had observed in practice. It turns out it
wasn't postgres it was the driver that was doing it.
Drivers obviously have no way to disambiguate either so apparently they just
return the last column by the specified name. The problem is this doesn't help
me when I want to use the column in an ORDER BY clause or elsewhere in the
query.
Is there any help in the SQL spec on this? Some syntax for disambiguating
references or removing columns from the output list?
Otherwise, I think Postgres should behave differently in this case:
For example:
slo=> select * from (select 1 as foo, 2 as foo);
ERROR: subquery in FROM must have an alias
HINT: For example, FROM (SELECT ...) [AS] foo.
slo=> select * from (select 1 as foo, 2 as foo) as x;foo | foo
-----+----- 1 | 2
(1 row)
What purpose is there to returning both columns to the outer query? The
columns become effectively inaccessible. There's no syntax for disambiguating
any reference.
I think postgres should treat the second alias as hiding the first. Currently
there's no way to selectively override a single output column. The only way to
do is to put your query in a subquery and list every single output column
again except the one you want to override.
Note that I'm not saying Postgres should remove ambiguous columns from
different tables for the inner query. Only for subsequent layers where they
have no way to access them anyways.
--
greg
