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

On 10/30/2011 10:00 PM, Christopher Browne wrote:
>
> There is legitimate reason to reject this on the basis of nondeterminism.
>
> While we are surely obliged to "hold our noses" and support "SELECT
> *", as:
> A) The SQL standard obliges us, and
> B) People already use it a lot,
>
> Neither of those factors hold true for the EXCLUDING notion. So all
> things are decidedly not equal.
>

Surely it's only non-deterministic to the extent that '*' itself is
non-deterministic. So your argument boils down to 'anything that
enhances * is bad,' ISTM.

> By all means I find it an interesting feature, but that shouldn't be
> mistaken for necessarily being a desirable feature.
>
> I don't think I wish it. We're telling our developers not to use
> "select *", and I don't think having "select * except " would change
> that policy, beyond requiring us to waste time explaining :
>
> "No, we're not changing policy. The fact that PGDG added this to 9.2
> does *not* imply our policy was wrong."
>

That's fine, and it's a good policy. A good policy might well exclude
use of a number of available features (e.g. one place I know bans doing
joins with ',' instead of explicit join operators). But I don't think it
helps us decide what to support.

The fact is that if you have 100 columns and want 95 of them, it's very
tedious to have to specify them all, especially for ad hoc queries where
the house SQL standards really don't matter that much. It's made more
tedious by the fact that there is no real help in constructing the
query. This gets particularly bad with views, which developers often
seem to stuff with every available column that might be needed by some
query instead of creating views tailored to particular queries. Not long
ago annoyance with this prompted my to write a little utility function
that would give me a query with all the columns specified so I could
cut and paste it, and delete the columns I didn't want. (Another
advantage is that the result is guaranteed typo free, which my typing
certainly is not.) See <https://gist.github.com/818490>. It's far from
perfect, but I still find myself using it several times a month, mainly
for the very purpose intended by this suggested feature.

cheers

andrew