> On Feb 25, 2020, at 2:14 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Paul Jungwirth <pj@xxxxxxxxxxxxxxxxxxxxxxxx> writes: >> Not that this is necessarily fatal, but you'd need to avoid parsing >> trouble with the other EXCEPT, e.g. >> SELECT 1 EXCEPT SELECT 1; > > Yeah, it doesn't sound like much consideration has been given to > that ambiguity, but it's a big problem if you want to use a syntax > like this. > >> Google Big Query was mentioned upthread. I see they require parens, e.g. >> SELECT ... EXCEPT (...). I don't think that actually fixes the ambiguity >> though. > > Indeed it doesn't, because you can parenthesize an EXCEPT's sub-queries: > > regression=# select 1 except (select 2); > ?column? > ---------- > 1 > (1 row) > > In principle, once you got to the SELECT keyword you could tell things > apart, but I'm afraid that might be too late for a Bison-based parser. > >> So it seems they require at least one `*` in the SELECT target list. In >> fact the `*` must be the very last thing. Personally I think it should >> be as general as possible and work even without a `*` (let alone caring >> about its position). > > I wonder if they aren't thinking of the EXCEPT as annotating the '*' > rather than the whole SELECT list. That seems potentially more flexible, > not less so. Consider > > SELECT t1.* EXCEPT (foo, bar), t2.* EXCEPT (baz) ... FROM t1, t2, ... > > This doesn't have any problem with ambiguity if t2 has a "foo" column, > or if t1 has a "baz" column; which indeed would be cases where this > sort of ability would be pretty useful, since otherwise you end up > with painful-to-rename duplicate output column names. And certainly > there is no particular need for this construct if you didn't write > a "*". > > regards, tom lane > OMIT rather than EXCEPT? >
