Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."? - Mailing list pgsql-hackers

From Darren Duncan
Subject Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
Date
Msg-id 4EADA3B8.7050704@darrenduncan.net
Whole thread Raw
In response to Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?  (David Wilson <david.t.wilson@gmail.com>)
Responses Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Eric Ridge
Date:
Subject: Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
Next
From: Eric Ridge
Date:
Subject: Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?