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

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

From
Stanislav Motyčka
Date:
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

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

From
Josef Šimánek
Date:

út 25. 2. 2020 v 11:18 odesílatel Stanislav Motyčka <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

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

From
Fabio Ugo Venchiarutti
Date:
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.



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

From
Miles Elam
Date:
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:
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

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

From
Josef Šimánek
Date:


ú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?).
 
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:
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

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

From
Miles Elam
Date:

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.

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

From
Paul A Jungwirth
Date:
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



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

From
Guyren Howe
Date:


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



ul[class*='mb-extra__public-links'], ul[class*='mb-note__public-links'], ul[class*='mb-task__public-links'] { display: none !important; }

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

From
Stanislav Motycka
Date:


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

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

From
Alvaro Herrera
Date:
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



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

From
Paul Jungwirth
Date:
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



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

From
Tom Lane
Date:
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



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

From
Rob Sargent
Date:


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

>



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

From
Pavel Stehule
Date:


ú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