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

Pavel Stehule wrote:
> 2011/10/30 Darren Duncan <darren(at)darrenduncan(dot)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