Skip site navigation (1) Skip section navigation (2)

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

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Christopher Browne <cbbrowne(at)gmail(dot)com>
Cc: Darren Duncan <darren(at)darrenduncan(dot)net>, Eric Ridge <eebbrr(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Mark Mielke <mark(at)mark(dot)mielke(dot)cc>, David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
Date: 2011-10-31 13:14:48
Message-ID: 4EAE9F48.9090702@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-hackers

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

In response to

Responses

pgsql-hackers by date

Next:From: Anssi KääriäinenDate: 2011-10-31 13:51:39
Subject: Re: So, is COUNT(*) fast now?
Previous:From: Robert HaasDate: 2011-10-31 12:44:16
Subject: Re: So, is COUNT(*) fast now?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group