From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Paul Jungwirth <pj(at)illuminatedcomputing(dot)com>, Stanislav Motycka <stanislav(dot)motycka(at)gmail(dot)com>, PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]] |
Date: | 2020-02-26 05:29:21 |
Message-ID: | CAFj8pRCvp1o3nvpYa1Axn+nSOUNGdpJxBrqSkSxOd+RBPXK3Ng@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
út 25. 2. 2020 v 22:14 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
> Paul Jungwirth <pj(at)illuminatedcomputing(dot)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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Ho | 2020-02-26 05:57:49 | Re: Backup & Restore |
Previous Message | Eric Gillum | 2020-02-26 04:53:58 | information_schema performance in Postgres 12 |