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

That makes sense, however for my general use case I'd also like the
ability to mark some columns as not match-able by `SELECT * FROM
<table>` and `TABLE <table>`  at table definition without having to
create dedicated views (think of the way system attributes such as
tableoid, cmax, cmin ... are handled) .



Typical use case: manual inspection of rows containing an hefty payload
field and some metadata ones; the payload more often than not isn't the
bit I'm interested in, but the size of it dominates the output making it
hard to read (granted - psql has formatting options to handle that, but
having a sane default at the table level would help a lot).



One may argue that such behaviour breaks the principle of least
surprise, so all of this should probably be strictly opt-in (and perhaps
queries could output some hints that such hidden columns exists).



Regards


F



On 25/02/2020 10:30, Josef Šimánek wrote:
> Just to mention, similar concept can be found in Google BigQuery.
>
> https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select-except
> https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select-replace
>
> út 25. 2. 2020 v 11:18 odesílatel Stanislav Motyčka
> <stanislav.motycka@gmail.com <mailto:stanislav.motycka@gmail.com>> napsal:
>
>     Hello,
>
>     Sometimes (for tables with many columns) it would be better and
>     easier to write "SELECT" statement with clause "EXCEPT":
>     "SELECT * [EXCEPT col1 [,col2]] FROM ..."
>
>     It's easier to write "except" one or two columns from all (*) as to
>     write names of all columns besides one or two.
>     What do you thin about it?
>
>     Best regards
>     Stano Motycka
>

--
Regards

Fabio Ugo Venchiarutti
OSPCFC Network Engineering Dpt.
Ocado Technology

--


Notice:
This email is confidential and may contain copyright material of
members of the Ocado Group. Opinions and views expressed in this message
may not necessarily reflect the opinions and views of the members of the
Ocado Group.

If you are not the intended recipient, please notify us
immediately and delete all copies of this message. Please note that it is
your responsibility to scan this message for viruses.

References to the
"Ocado Group" are to Ocado Group plc (registered in England and Wales with
number 7098618) and its subsidiary undertakings (as that expression is
defined in the Companies Act 2006) from time to time. The registered office
of Ocado Group plc is Buildings One & Two, Trident Place, Mosquito Way,
Hatfield, Hertfordshire, AL10 9UL.

On Tue, Feb 25, 2020 at 2:18 AM Stanislav Motyčka
<stanislav.motycka@gmail.com> wrote:
> Sometimes (for tables with many columns) it would be better and easier to write "SELECT" statement with clause
"EXCEPT":
> "SELECT * [EXCEPT col1 [,col2]] FROM ..."

I've wanted this feature lots of times and would be delighted to see
it in Postgres.

On Tue, Feb 25, 2020 at 6:51 AM Miles Elam <miles.elam@productops.com> wrote:
> 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"
> ?

I take the proposal to mean this:

SELECT listOfColumns [EXCEPT listOfColumns] FROM ...

not this:

SELECT listOfColumns [EXCEPT (listOfColumns) [listOfColumns [EXCEPT
(listOfColumns)]]]... FROM ...

So there is always a single EXCEPT clause (if any) and it comes after
the entire SELECT clause. Then there is no ambiguity. Also this
approach makes the feature easy to understand and use. I don't see any
benefit to letting people interleave selected & excepted columns.

Regards,
Paul

I take the proposal to mean this:

SELECT listOfColumns [EXCEPT listOfColumns] FROM ...

On 2020-Feb-25, Stanislav Motyčka wrote:

> Sometimes (for tables with many columns) it would be better and easier
> to write "SELECT" statement with clause "EXCEPT":
> "SELECT * [EXCEPT col1 [,col2]] FROM ..."

I think an important initial question is how do other database systems
implement this functionality, if they do, and what syntax do they offer.
From there we can move on to the conversation of where is the ISO SQL
committee going about this.

I think it's good to extend the standard to some extent, but it would
not do to have it extended in a direction that ends up contrary to what
they pursue in the future.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2/25/20 11:46 AM, Stanislav Motycka wrote:
> 
> 
> Dňa 25. 2. 2020 o 20:28 Paul A Jungwirth napísal(a):
>> I take the proposal to mean this:
>>
>> SELECT listOfColumns [EXCEPT listOfColumns] FROM ...
> Exactly, simply exclude unneeded columns from the base clause "SELECT", 
> nothing more ..

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;

Believe it or not these are valid SQL:

SELECT;
SELECT EXCEPT SELECT;

This fails today but only because of the different number of columns:

SELECT 1 AS SELECT EXCEPT SELECT;

So the parser understands it as selectQuery EXCEPT selectQuery, but you 
can see how it could also be parsable as this new structure. So the 
parser would have to decide which is meant (if that's even possible at 
that early stage).

I guess as soon as you exclude two columns it is unambiguous though 
because of this comma: SELECT ... EXCEPT SELECT, .... And anyway I think 
for such a pathological case you could just tell people to add double 
quotes.

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. Also a few other notes (after very brief testing):

SELECT * EXCEPT (foo) FROM t;    -- works
SELECT * EXCEPT (foo, bar) FROM t;    -- works
SELECT t.* EXCEPT (foo) FROM t;    -- works
SELECT * EXCEPT foo FROM t;    -- fails
SELECT foo, bar EXCEPT (foo) FROM t;    -- fails
SELECT t1.foo, t2.* EXCEPT (foo) FROM t1 JOIN t2 ON ...; -- works
SELECT t2.*, t1.foo EXCEPT (foo) FROM t1 JOIN t2 ON ...; -- fails!

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).

Regards,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com

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 "*".

            regards, tom lane

> On Feb 25, 2020, at 2:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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 "*".
>
>            regards, tom lane
>

OMIT rather than EXCEPT?

>