Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]]

On Tue, Feb 25, 2020 at 6:41 AM Josef Šimánek <josef(dot)simanek(at)gmail(dot)com>
wrote:

>
> út 25. 2. 2020 v 15:35 odesílatel Miles Elam <miles(dot)elam(at)productops(dot)com>
> napsal:
>
>> How do you see this syntax working in a JOIN query?
>>
>> SELECT x.* EXCEPT x.col1, x.col2, y.col1
>> FROM tablex AS x
>> LEFT JOIN tabley AS y;
>>
>> The column(s) you want to exclude become ambiguous.
>>
>
> Can you explain how are those column(s) ambiguous in your example? I would
> expect to select everything from table x (as SELECT x.* should do) except
> x.col1 and x.col2. Nothing is selected from table y thus y.col1 is not
> relevant here (the question is if this is problem or not - raise, ignore?).
>

Do you mean
"select everything from tablex except for tablex.col1, and also select
tablex.col2 and tabley.col1"
or
"select everything from tablex except for tablex.col1 AND tablex.col2,
and also select tabley.col1"
?

It's entirely possible to specify a column twice. It's quite common for me
to see what fields I need from a table by doing a "SELECT * ... LIMIT 1"
and then "SELECT col1, * ... LIMIT 1" as I refine the query, eventually
eliminating the wildcard when I'm done. (When I'm using an IDE that doesn't
support SQL table/column autocomplete.)

EXCEPT would need to be scoped as to which columns it's meant to be
excluding without ambiguity. Just reading from the column list until you
hit another table's columns or a function strikes me as far too loose.