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

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.