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

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> napsal:

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.

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. Parentheses?

SELECT x.* EXCEPT (x.col1, x.col2), y.col1
FROM tablex AS x
LEFT JOIN tabley AS y;

Could work, but this is encouraging the use of the wildcard selector, which
I'm not sure is a productive or maintainable goal. In exchange for
flexibility, you've added a non-trivial amount of comprehension complexity.
I'm not a big fan of the wildcard selector except in the most trivial cases
and even then only as part of development toward a final query with all
columns specified. Then again I try not to have tables with hundreds of
columns (or even tens in most cases), so my own use cases may bias
me. Personally I just don't like queries where I cannot clearly see what it
being returned to me. Anything that makes that ambiguity more popular will
be viewed with a skeptical eye.

On Tue, Feb 25, 2020 at 2:18 AM Stanislav Motyčka <
stanislav.motycka@gmail.com> wrote:

út 25. 2. 2020 v 15:35 odesílatel Miles Elam <miles.elam@productops.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?).

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

út 25. 2. 2020 v 15:35 odesílatel Miles Elam <miles.elam@productops.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.

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

On Feb 25, 2020, at 11:28 , Paul A Jungwirth <pj@illuminatedcomputing.com> wrote:

On Tue, Feb 25, 2020 at 2:18 AM Stanislav Motyčka
<stanislav.motycka@gmail.com <mailto: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 …"

The single biggest benefit is that I could write many more views that don’t need to be changed when I change the underlying table.

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

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?

ú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