Thoughts on "SELECT * EXCLUDING (...) FROM ..."?

Eric,

* Eric Ridge (eebbrr@gmail.com) wrote:

It seems like such a syntax would better document the
intent of a query too, rather than leaving one wondering if "big_col1"
was supposed to be omitted from the target list or not.

Well, I expect most here would say that any application query should be
specific about exactly what columns it wants (iow- don't use select * in
your apps :). As for it being useful in psql, I could see that.

Anyways, I just wanted to run the idea by youse guys before I put too
much more effort into it. I've already made what appear to be the
minimum necessary changes to gram.y, and a few quick greps through the
code make me think the rest will be pretty easy.

Maybe the SQL spec says something about this and nobody's done the work yet?

I don't know of anything like this in the spec. Also, there would be
concern about this possibly going against spec, breaking possibly valid
queries, promoting keywords to reserved words, and maybe ending up in a
bad situation if the SQL committee decides to support that kind of
syntax for something completely different.

In general, I doubt this is something we'd implement, but others may
feel differently. What might be interesting to consider is how hard it
would be to make psql smarter when it comes to line editing in this
regard. Maybe if there was a way to easily expand the '*' from psql and
then you could remove the columns from the list easily..?

Thanks,

Stephen

On Sat, Oct 29, 2011 at 6:35 PM, Stephen Frost <sfrost@snowman.net> wrote:

Maybe the SQL spec says something about this and nobody's done the work yet?

I don't know of anything like this in the spec.  Also, there would be
concern about this possibly going against spec, breaking possibly valid
queries, promoting keywords to reserved words, and maybe ending up in a
bad situation if the SQL committee decides to support that kind of
syntax for something completely different.

At least concerning breaking valid queries and promoting keywords, I
don't think the former can happen (they'd fail to parse today) and the
latter doesn't seem necessary as "EXCLUDING"'s use in this case
appears to be completely unambiguous.

However, I realize there's no second-guessing what the SQL committee
might do in the future.

In general, I doubt this is something we'd implement, but others may
feel differently.

I hope so. :)

What might be interesting to consider is how hard it
would be to make psql smarter when it comes to line editing in this
regard.  Maybe if there was a way to easily expand the '*' from psql and
then you could remove the columns from the list easily..?

Probably really dang hard, especially when you consider a "SELECT *"
involving lots of joins. And even if it turned out to be easy, it
would be limited to psql.

Anyways, it's just something I've wanted for quite awhile and thought
I'd actually do the work to make it happen, *if* y'all would take it.

eric

I agree that this feature would be quite useful and should be included in SQL.
The exact syntax is less of an issue, but just the ability to cleanly say
"select all columns except for these". I have in fact argued for the same
feature in the past.

If you want to and can implement this feature then more power to you. I'll look
forward to it being in Pg 9.2.

I think then the only discussion point should be what (terse) syntax to use for
it, not whether the feature should exist at all.

Arguing against this feature is like arguing against supporting "where not()" or
"except" or "not in". One should be able to do complements not only of rows but
of columns too. Basic good language design.

-- Darren Duncan

Eric Ridge wrote:

2011/10/30 Darren Duncan <darren@darrenduncan.net>:

I agree that this feature would be quite useful and should be included in
SQL. The exact syntax is less of an issue, but just the ability to cleanly
say "select all columns except for these".  I have in fact argued for the
same feature in the past.

If you want to and can implement this feature then more power to you.  I'll
look forward to it being in Pg 9.2.

I think then the only discussion point should be what (terse) syntax to use
for it, not whether the feature should exist at all.

Arguing against this feature is like arguing against supporting "where
not()" or "except" or "not in".  One should be able to do complements not
only of rows but of columns too.  Basic good language design.

My practice speaks so this is not true - I don't know only bad
designed projects or very bad designed projects that needs too.

I don't see any reason why do it on SQL level.

It can sence only in psql as same special filter - if we would to
enhace a report features there.

Regards

Pavel Stehule

On 10/29/2011 03:26 PM, Eric Ridge wrote:

Would y'all accept a patch that extended the "SELECT *" syntax to let
you list fields to exclude from the A_Star?

Quite regularly I'll be testing queries via psql and want to see all
the columns from a fairly wide table except maybe a giant text or xml
column. A syntax like:

SELECT * EXCLUDING (big_col1, big_col2) FROM foo;

If it is quite regular I would actually argue two things:

1. Use a view
2. You haven't normalized correctly

I am not trying to be a pedantic zealot or anything but those would be
my arguments against.

Sincerely,

Joshua D. Drake
--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

Pavel Stehule wrote:

2011/10/30 Darren Duncan <darren@darrenduncan.net>:

I agree that this feature would be quite useful and should be included in
SQL. The exact syntax is less of an issue, but just the ability to cleanly
say "select all columns except for these". I have in fact argued for the
same feature in the past.

If you want to and can implement this feature then more power to you. I'll
look forward to it being in Pg 9.2.

I think then the only discussion point should be what (terse) syntax to use
for it, not whether the feature should exist at all.

Arguing against this feature is like arguing against supporting "where
not()" or "except" or "not in". One should be able to do complements not
only of rows but of columns too. Basic good language design.

My practice speaks so this is not true - I don't know only bad
designed projects or very bad designed projects that needs too.

I don't see any reason why do it on SQL level.

It can sence only in psql as same special filter - if we would to
enhace a report features there.

The SQL level is exactly the correct and proper place to do this.

Its all about mathematical parity. That is the primary reason to do it.

- "SELECT *" gives you a whole set.
- "SELECT foo, bar" gives you a subset of that.
- "SELECT ALL BUT foo, bar" gives you the complementary subset.

There's a variety of uses for specifying complementary subsets, and when the
clean syntax is available, people will start using it for cleaner code, even if
they previously had workarounds.

The complementary subset should be implemented in exactly the same place and
manner as the forward subset, on the SQL level.

Arguing against this is like arguing against a subtraction operator, because we
can emulate using addition plus negation, or saying subtraction should just be a
special filter in a client app.

-- Darren Duncan

On Sun, Oct 30, 2011 at 1:10 AM, Darren Duncan <darren@darrenduncan.net> wrote:

The SQL level is exactly the correct and proper place to do this.

Its all about mathematical parity.  That is the primary reason to do it.

- "SELECT *" gives you a whole set.
- "SELECT foo, bar" gives you a subset of that.
- "SELECT ALL BUT foo, bar" gives you the complementary subset.

That's not actually entirely true given the usual SQL (and
mathematical) meaning of "set". This feature relates to the set of
attributes returned regarding elements of the returned set, not the
set itself- the actual returned set is identical regardless of the
column-specifier formulation. Claiming this as an SQL mathematical
purity issue is a bit disingenuous, as SQL set manipulation takes
place at the member level rather than the attribute level- SQL is
otherwise quite explicit about requiring explicit listings of the
attributes that the client is interested in regarding a returned set
of member rows.

Arguing against this is like arguing against a subtraction operator, because
we can emulate using addition plus negation, or saying subtraction should
just be a special filter in a client app.

That would be true if this was an argument against "WHERE" or
"EXCEPT". Column specification and row specification are very
different and cannot be conflated.

That's not to say this proposal is without merit, merely that your
arguments for it are poorly founded and not particularly relevant.

--
- David T. Wilson
david.t.wilson@gmail.com

On Oct 30, 2011, at 12:53 AM, Joshua D. Drake wrote:

If it is quite regular I would actually argue two things:

1. Use a view
2. You haven't normalized correctly

I am not trying to be a pedantic zealot or anything but those would be my arguments against.

You know how general database work goes. For every situation where you can make a view or every situation where you should normalize, there's at least one corresponding situation where you can't. All database systems, Postgres included, give us plenty of ways to do things "wrong", many of which are much worse than this little idea.

I guess I'd like for everyone to evaluate the idea on the value it could provide to Postgres and its users, rather than imposing philosophical/religious beliefs about "correct" database schema design.

I'm regularly tasked with debugging queries, analyzing, exporting, and otherwise transforming data into something a customer wants. I'd use something like "SELECT * EXCLUDING (…)" on a *daily* basis. I'm sick and tired of stuff like:

psql -tA db -c "\d table" | cut -f1 -d\| | grep -v col | tr \\n ,

just to exclude one column from a list of maybe 100. And if multiple tables are involved in the query, it just gets that much more complicated. I'd rather do:

SELECT * EXCLUDING (x.col) FROM ( <giant application-generated query> ) x;

Then, once I verify it's all good:

COPY ( SELECT * EXCLUDING (x.col) FROM ( <giant application-generated query> ) x ) TO '/tmp/foo.out' WITH CSV;

Anyways, looks like it might be an uphill battle to get the idea accepted (let alone any code I write!), but I ain't givin' up just yet.

eric

PROPRIETARY AND COMPANY CONFIDENTIAL COMMUNICATIONS
The information contained in this communication is intended only for
the use of the addressee. Any other use is strictly prohibited.
Please notify the sender if you have received this message in error.
This communication is protected by applicable legal privileges and is
company confidential.

On Sun, Oct 30, 2011 at 1:51 PM, Eric B. Ridge <ebr@tcdi.com> wrote:

eric

PROPRIETARY AND COMPANY CONFIDENTIAL COMMUNICATIONS

<snip>

my bad. Switched email accounts without realizing. :(

eric

On Oct 29, 2011, at 11:53 PM, Joshua D. Drake wrote:

On 10/29/2011 03:26 PM, Eric Ridge wrote:

Would y'all accept a patch that extended the "SELECT *" syntax to let
you list fields to exclude from the A_Star?

Quite regularly I'll be testing queries via psql and want to see all
the columns from a fairly wide table except maybe a giant text or xml
column. A syntax like:

SELECT * EXCLUDING (big_col1, big_col2) FROM foo;

If it is quite regular I would actually argue two things:

1. Use a view

If you know that you want all fields except X, Y and Z, why should you be forced to manually cut and paste all the other fields into the view definition? That's wasted time and effort that could better be spent elsewhere.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

On Sun, Oct 30, 2011 at 2:15 PM, Jim Nasby <jim@nasby.net> wrote:

If you know that you want all fields except X, Y and Z, why should you be forced to manually cut and paste all the other fields into the view definition? That's wasted time and effort that could better be spent elsewhere.

I agree. This isn't something I would recommend that people do in the
queries they embed in their application, but for ad-hoc queries it
comes up all the time.

OTOH, I'm slightly afraid of how much work it would take to implement
this properly.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Oct 30, 2011, at 11:54 AM, Robert Haas wrote:

If you know that you want all fields except X, Y and Z, why should you be forced to manually cut and paste all the other fields into the view definition? That's wasted time and effort that could better be spent elsewhere.

I agree. This isn't something I would recommend that people do in the
queries they embed in their application, but for ad-hoc queries it
comes up all the time.

It can also be very useful for generating queries that need to omit rows, such as in PL/pgSQL functions that use EXECUTE, not to mention client code.

Best,

David

I think the loose consensus I've picked up from people in this thread is that
the ability to tersely specify a complementary subset of columns is something
that is simple enough and wouldn't hurt us to have it but that its utility is
limited such that a lot of people wouldn't want to do the work to implement it
either.

Eric B. Ridge wrote:

Anyways, looks like it might be an uphill battle to get the idea accepted
(let alone any code I write!), but I ain't givin' up just yet.

I think this is the bottom line here.

The real question to ask ourselves is, if Eric Ridge is willing to do all the
work to implement this feature, and the code quality is up to the community
standards and doesn't break anything else, then will the code be accepted?

Its one thing to argue whether a new small feature is useful enough to go to the
trouble to implement, and its another thing to argue whether that feature is
harmful enough to reject a free working implementation (of otherwise conforming
code quality) from someone who has already gone to the trouble to implement it.

Eric, if you want to implement this, I say more power to you, and I will use it.

-- Darren Duncan

Darren Duncan <darren@darrenduncan.net> writes:

The real question to ask ourselves is, if Eric Ridge is willing to do all the
work to implement this feature, and the code quality is up to the community
standards and doesn't break anything else, then will the code be accepted?

It's entirely possible that it will get bounced on standards-compliance
grounds. In particular, I don't think it's acceptable to introduce a
new reserved keyword for this --- that would fall under the "fails to
not break anything else" category.

regards, tom lane

On Sun, Oct 30, 2011 at 2:54 PM, Robert Haas <robertmhaas@gmail.com> wrote:

OTOH, I'm slightly afraid of how much work it would take to implement
this properly.

I think first, the A_Star node struct will need to be expanded to
include a List of qualified column references to exclude. From there,
the "target_el" rule in gram.y will need to be expanded to support a
syntax like:
'*' EXCLUDING '(' columnref_list ')' { ... }
I also think that the "indirection_el" rule will need to be expanded
to support something similar.

Together, that would let us write both:
SELECT * EXCLUDING(table1.col1, table2.col1) FROM ...
and
SELECT table.* EXCLUDING(col1, col2) FROM ...
or even
SELECT * EXCLUDING(table1.col1), table2.* EXCLUDING(col1) FROM ...

I think changing the "indirection_el" rule might have an impact to
OLD/NEW, but I'm not sure. Is it legal to write OLD.*, and if so,
would you also want to write OLD.* EXCLUDING (...) in those cases? I
think this only applies to RULES or SQL-type trigger functions, but
not pl/pgsql?

Then it looks like touching various functions in src/backend/nodes/*.c
to do the right things with the new exclusion list field in A_Star. I
haven't traced through everything yet, but it looks like if the
various places in src/backend/nodes/*.c are done correctly, then
regurgitating a view definition or whatnot that includes this syntax
will be automatic (i.e., no specific support required for pg_dump)?

Anyways, at first I thought it would be about 8hrs of work just to get
something working. Maybe it's more like 20, but even still, it seems
fairly straightforward.

eric

David Wilson wrote:

On Sun, Oct 30, 2011 at 1:10 AM, Darren Duncan <darren@darrenduncan.net> wrote:

The SQL level is exactly the correct and proper place to do this.

Its all about mathematical parity. That is the primary reason to do it.

- "SELECT *" gives you a whole set.
- "SELECT foo, bar" gives you a subset of that.
- "SELECT ALL BUT foo, bar" gives you the complementary subset.

That's not actually entirely true given the usual SQL (and
mathematical) meaning of "set". This feature relates to the set of
attributes returned regarding elements of the returned set, not the
set itself- the actual returned set is identical regardless of the
column-specifier formulation. Claiming this as an SQL mathematical
purity issue is a bit disingenuous, as SQL set manipulation takes
place at the member level rather than the attribute level- SQL is
otherwise quite explicit about requiring explicit listings of the
attributes that the client is interested in regarding a returned set
of member rows.

SQL rowsets/tables are distinctly sets across two dimensions, both across
columns and across rows. You have sets of rows and sets of columns at the same
time. And it is useful to slice the rowset/table along either or both
dimension, wherein you produce a subset in that dimension. We presently have
the terse syntax for specifying both the subset we do want and the subset we
want the complement of, for rows, but not for columns. It is true that columns
and rows are not the same, but they are both still sets.

Arguing against this is like arguing against a subtraction operator, because
we can emulate using addition plus negation, or saying subtraction should
just be a special filter in a client app.

That would be true if this was an argument against "WHERE" or
"EXCEPT". Column specification and row specification are very
different and cannot be conflated.

Well I raised the WHERE/EXCEPT analogy in my initial reply.

Not conflating rows and columns is why we have different syntax to work with them.

That's not to say this proposal is without merit, merely that your
arguments for it are poorly founded and not particularly relevant.

I disagree, but regardless, other arguments have been made for the feature that
are more based in utility, and I agree with those, how having the feature can
save a lot of users a lot of work.

-- Darren Duncan

On Sun, Oct 30, 2011 at 3:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

It's entirely possible that it will get bounced on standards-compliance
grounds.

And that's a perfectly valid reason to reject it.

In particular, I don't think it's acceptable to introduce a
new reserved keyword for this --- that would fall under the "fails to
not break anything else" category.

Please correct me if I'm wrong, but if we choose the word carefully
(which is why I chose EXCLUDING), I think we're okay? EXCLUDING is
already defined as an "ordinary key word". And it's new use in this
situation seems to be completely unambiguous, such that you'd still be
able to use "excluding" everywhere you already could.

You know more about the grammar than I (or probably most anyone), so
I'm wondering why you think it might need to be a "reserved keyword"?
Alternatively, would it be okay to use an existing reserved keyword?

eric

Tom Lane wrote:

Darren Duncan <darren@darrenduncan.net> writes:

The real question to ask ourselves is, if Eric Ridge is willing to do all the
work to implement this feature, and the code quality is up to the community
standards and doesn't break anything else, then will the code be accepted?

It's entirely possible that it will get bounced on standards-compliance
grounds. In particular, I don't think it's acceptable to introduce a
new reserved keyword for this --- that would fall under the "fails to
not break anything else" category.

regards, tom lane

Well then we come up with a (SQL-level) syntax for the feature that doesn't
introduce new reserved keywords.

As I said before, the important thing is to have the feature, and that the exact
syntax is the main point to discuss.

Postgres already has a number of syntactic features that aren't in the SQL
standard and coexist, and so we add one of those.

-- Darren Duncan

Stupid question:

Is this just a display thing? Or does this have impact for things such
as COUNT(*) vs COUNT(1)?

Is it "like a view, but on the fly"?

I'm found myself in the *occasional* (certainly not daily!) situation
where such a feature might be useful, but each time I wonder about if
there should be a better way, I realize that if I ever saw such a thing
in production code it would be the first code I shot down. 1) Not
standards compliant, 2) Not deterministic (i.e. a database change might
cause my code to break), 3) Working around a problem that maybe
shouldn't exist in the first place? It's a like buying a rug, so that
nobody sees the scratches on the floor.

I can see the contention. :-)

If it existed, I would occasionally use it from the command line. I'm
thinking three times a year. Certainly not daily. Heck, if it's more
characters to type (than "select * ...") the number of times I would
bother typing it are quite short. :-)

Cheers,

--
Mark Mielke<mark@mielke.cc>