[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]]
From:       Pavel Stehule <pavel.stehule () gmail ! com>
Date:       2020-02-26 5:29:21
Message-ID: CAFj8pRCvp1o3nvpYa1Axn+nSOUNGdpJxBrqSkSxOd+RBPXK3Ng () mail ! gmail ! com
[Download RAW message or body]

út 25. 2. 2020 v 22:14 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

> Paul Jungwirth <pj@illuminatedcomputing.com> 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 "*".
>

this proposal looks well

Pavel


>                         regards, tom lane
>
>
>

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">út 25. 2. 2020 v  22:14 odesílatel Tom Lane &lt;<a \
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; \
napsal:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Paul Jungwirth &lt;<a \
href="mailto:pj@illuminatedcomputing.com" \
target="_blank">pj@illuminatedcomputing.com</a>&gt; writes:<br> &gt; Not that this is \
necessarily fatal, but you&#39;d need to avoid parsing <br> &gt; trouble with the \
other EXCEPT, e.g.<br> &gt; SELECT 1 EXCEPT SELECT 1;<br>
<br>
Yeah, it doesn&#39;t sound like much consideration has been given to<br>
that ambiguity, but it&#39;s a big problem if you want to use a syntax<br>
like this.<br>
<br>
&gt; Google Big Query was mentioned upthread. I see they require parens, e.g. <br>
&gt; SELECT ... EXCEPT (...). I don&#39;t think that actually fixes the ambiguity \
<br> &gt; though.<br>
<br>
Indeed it doesn&#39;t, because you can parenthesize an EXCEPT&#39;s sub-queries:<br>
<br>
regression=# select 1 except (select 2);<br>
  ?column? <br>
----------<br>
            1<br>
(1 row)<br>
<br>
In principle, once you got to the SELECT keyword you could tell things<br>
apart, but I&#39;m afraid that might be too late for a Bison-based parser.<br>
<br>
&gt; So it seems they require at least one `*` in the SELECT target list. In <br>
&gt; fact the `*` must be the very last thing. Personally I think it should <br>
&gt; be as general as possible and work even without a `*` (let alone caring <br>
&gt; about its position).<br>
<br>
I wonder if they aren&#39;t thinking of the EXCEPT as annotating the &#39;*&#39;<br>
rather than the whole SELECT list.   That seems potentially more flexible,<br>
not less so.   Consider<br>
<br>
SELECT t1.* EXCEPT (foo, bar), t2.* EXCEPT (baz) ... FROM t1, t2, ...<br>
<br>
This doesn&#39;t have any problem with ambiguity if t2 has a &quot;foo&quot; \
column,<br> or if t1 has a &quot;baz&quot; column; which indeed would be cases where \
this<br> sort of ability would be pretty useful, since otherwise you end up<br>
with painful-to-rename duplicate output column names.   And certainly<br>
there is no particular need for this construct if you didn&#39;t write<br>
a &quot;*&quot;.<br></blockquote><div><br></div><div>this proposal looks \
well</div><div><br></div><div>Pavel</div><div><br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"> <br>
                                    regards, tom lane<br>
<br>
<br>
</blockquote></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]