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

Would y'all accept a patch that extended the "SELECT *" syntax to let
you list fields to exclude from the A_Star?

Quite regularly I'll be testing queries via psql and want to see all
the columns from a fairly wide table except maybe a giant text or xml
column. A syntax like:

SELECT * EXCLUDING (big_col1, big_col2) FROM foo;

would be pretty handy. It would definitely save some typing in
certain cases. It seems like such a syntax would better document the
intent of a query too, rather than leaving one wondering if "big_col1"
was supposed to be omitted from the target list or not.

Anyways, I just wanted to run the idea by youse guys before I put too
much more effort into it. I've already made what appear to be the
minimum necessary changes to gram.y, and a few quick greps through the
code make me think the rest will be pretty easy.

Maybe the SQL spec says something about this and nobody's done the work yet?

Thanks for your input!

eric

Eric,

* Eric Ridge (eebbrr(at)gmail(dot)com) wrote:
> It seems like such a syntax would better document the
> intent of a query too, rather than leaving one wondering if "big_col1"
> was supposed to be omitted from the target list or not.

Well, I expect most here would say that any application query should be
specific about exactly what columns it wants (iow- don't use select * in
your apps :). As for it being useful in psql, I could see that.

> Anyways, I just wanted to run the idea by youse guys before I put too
> much more effort into it. I've already made what appear to be the
> minimum necessary changes to gram.y, and a few quick greps through the
> code make me think the rest will be pretty easy.
>
> Maybe the SQL spec says something about this and nobody's done the work yet?

I don't know of anything like this in the spec. Also, there would be
concern about this possibly going against spec, breaking possibly valid
queries, promoting keywords to reserved words, and maybe ending up in a
bad situation if the SQL committee decides to support that kind of
syntax for something completely different.

In general, I doubt this is something we'd implement, but others may
feel differently. What might be interesting to consider is how hard it
would be to make psql smarter when it comes to line editing in this
regard. Maybe if there was a way to easily expand the '*' from psql and
then you could remove the columns from the list easily..?

Thanks,

Stephen

On Sat, Oct 29, 2011 at 6:35 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> Maybe the SQL spec says something about this and nobody's done the work yet?
>
> I don't know of anything like this in the spec.  Also, there would be
> concern about this possibly going against spec, breaking possibly valid
> queries, promoting keywords to reserved words, and maybe ending up in a
> bad situation if the SQL committee decides to support that kind of
> syntax for something completely different.

At least concerning breaking valid queries and promoting keywords, I
don't think the former can happen (they'd fail to parse today) and the
latter doesn't seem necessary as "EXCLUDING"'s use in this case
appears to be completely unambiguous.

However, I realize there's no second-guessing what the SQL committee
might do in the future.

> In general, I doubt this is something we'd implement, but others may
> feel differently.

I hope so. :)

> What might be interesting to consider is how hard it
> would be to make psql smarter when it comes to line editing in this
> regard.  Maybe if there was a way to easily expand the '*' from psql and
> then you could remove the columns from the list easily..?

Probably really dang hard, especially when you consider a "SELECT *"
involving lots of joins. And even if it turned out to be easy, it
would be limited to psql.

Anyways, it's just something I've wanted for quite awhile and thought
I'd actually do the work to make it happen, *if* y'all would take it.

eric

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.

-- Darren Duncan

Eric Ridge wrote:
> Would y'all accept a patch that extended the "SELECT *" syntax to let
> you list fields to exclude from the A_Star?
>
> Quite regularly I'll be testing queries via psql and want to see all
> the columns from a fairly wide table except maybe a giant text or xml
> column. A syntax like:
>
> SELECT * EXCLUDING (big_col1, big_col2) FROM foo;
>
> would be pretty handy. It would definitely save some typing in
> certain cases. It seems like such a syntax would better document the
> intent of a query too, rather than leaving one wondering if "big_col1"
> was supposed to be omitted from the target list or not.
>
> Anyways, I just wanted to run the idea by youse guys before I put too
> much more effort into it. I've already made what appear to be the
> minimum necessary changes to gram.y, and a few quick greps through the
> code make me think the rest will be pretty easy.
>
> Maybe the SQL spec says something about this and nobody's done the work yet?
>
> Thanks for your input!
>
> eric
>

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.

Regards

Pavel Stehule

>
> -- Darren Duncan
>
> Eric Ridge wrote:
>>
>> Would y'all accept a patch that extended the "SELECT *" syntax to let
>> you list fields to exclude from the A_Star?
>>
>> Quite regularly I'll be testing queries via psql and want to see all
>> the columns from a fairly wide table except maybe a giant text or xml
>> column.  A syntax like:
>>
>>     SELECT * EXCLUDING (big_col1, big_col2) FROM foo;
>>
>> would be pretty handy.  It would definitely save some typing in
>> certain cases.  It seems like such a syntax would better document the
>> intent of a query too, rather than leaving one wondering if "big_col1"
>> was supposed to be omitted from the target list or not.
>>
>> Anyways, I just wanted to run the idea by youse guys before I put too
>> much more effort into it.  I've already made what appear to be the
>> minimum necessary changes to gram.y, and a few quick greps through the
>> code make me think the rest will be pretty easy.
>>
>> Maybe the SQL spec says something about this and nobody's done the work
>> yet?
>>
>> Thanks for your input!
>>
>> eric
>>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 10/29/2011 03:26 PM, Eric Ridge wrote:
>
> Would y'all accept a patch that extended the "SELECT *" syntax to let
> you list fields to exclude from the A_Star?
>
> Quite regularly I'll be testing queries via psql and want to see all
> the columns from a fairly wide table except maybe a giant text or xml
> column. A syntax like:
>
> SELECT * EXCLUDING (big_col1, big_col2) FROM foo;
>

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.

Sincerely,

Joshua D. Drake
--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

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

On Sun, Oct 30, 2011 at 1:10 AM, Darren Duncan <darren(at)darrenduncan(dot)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.

>
> 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.

That's not to say this proposal is without merit, merely that your
arguments for it are poorly founded and not particularly relevant.

--
- David T. Wilson
david(dot)t(dot)wilson(at)gmail(dot)com

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.

On Sun, Oct 30, 2011 at 1:51 PM, Eric B. Ridge <ebr(at)tcdi(dot)com> wrote:

> eric
>
> PROPRIETARY AND COMPANY CONFIDENTIAL COMMUNICATIONS

<snip>

my bad. Switched email accounts without realizing. :(

eric

On Oct 29, 2011, at 11:53 PM, Joshua D. Drake wrote:
> On 10/29/2011 03:26 PM, Eric Ridge wrote:
>>
>> Would y'all accept a patch that extended the "SELECT *" syntax to let
>> you list fields to exclude from the A_Star?
>>
>> Quite regularly I'll be testing queries via psql and want to see all
>> the columns from a fairly wide table except maybe a giant text or xml
>> column. A syntax like:
>>
>> SELECT * EXCLUDING (big_col1, big_col2) FROM foo;
>>
>
> If it is quite regular I would actually argue two things:
>
> 1. Use a view

If you know that you want all fields except X, Y and Z, why should you be forced to manually cut and paste all the other fields into the view definition? That's wasted time and effort that could better be spent elsewhere.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Sun, Oct 30, 2011 at 2:15 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> If you know that you want all fields except X, Y and Z, why should you be forced to manually cut and paste all the other fields into the view definition? That's wasted time and effort that could better be spent elsewhere.

I agree. This isn't something I would recommend that people do in the
queries they embed in their application, but for ad-hoc queries it
comes up all the time.

OTOH, I'm slightly afraid of how much work it would take to implement
this properly.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Oct 30, 2011, at 11:54 AM, Robert Haas wrote:

>> If you know that you want all fields except X, Y and Z, why should you be forced to manually cut and paste all the other fields into the view definition? That's wasted time and effort that could better be spent elsewhere.
>
> I agree. This isn't something I would recommend that people do in the
> queries they embed in their application, but for ad-hoc queries it
> comes up all the time.

It can also be very useful for generating queries that need to omit rows, such as in PL/pgSQL functions that use EXECUTE, not to mention client code.

Best,

David

I think the loose consensus I've picked up from people in this thread is that
the ability to tersely specify a complementary subset of columns is something
that is simple enough and wouldn't hurt us to have it but that its utility is
limited such that a lot of people wouldn't want to do the work to implement it
either.

Eric B. Ridge wrote:
> 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.

I think this is the bottom line here.

The real question to ask ourselves is, if Eric Ridge is willing to do all the
work to implement this feature, and the code quality is up to the community
standards and doesn't break anything else, then will the code be accepted?

Its one thing to argue whether a new small feature is useful enough to go to the
trouble to implement, and its another thing to argue whether that feature is
harmful enough to reject a free working implementation (of otherwise conforming
code quality) from someone who has already gone to the trouble to implement it.

Eric, if you want to implement this, I say more power to you, and I will use it.

-- Darren Duncan

Darren Duncan <darren(at)darrenduncan(dot)net> writes:
> The real question to ask ourselves is, if Eric Ridge is willing to do all the
> work to implement this feature, and the code quality is up to the community
> standards and doesn't break anything else, then will the code be accepted?

It's entirely possible that it will get bounced on standards-compliance
grounds. In particular, I don't think it's acceptable to introduce a
new reserved keyword for this --- that would fall under the "fails to
not break anything else" category.

regards, tom lane

On Sun, Oct 30, 2011 at 2:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> OTOH, I'm slightly afraid of how much work it would take to implement
> this properly.

I think first, the A_Star node struct will need to be expanded to
include a List of qualified column references to exclude. From there,
the "target_el" rule in gram.y will need to be expanded to support a
syntax like:
'*' EXCLUDING '(' columnref_list ')' { ... }
I also think that the "indirection_el" rule will need to be expanded
to support something similar.

Together, that would let us write both:
SELECT * EXCLUDING(table1.col1, table2.col1) FROM ...
and
SELECT table.* EXCLUDING(col1, col2) FROM ...
or even
SELECT * EXCLUDING(table1.col1), table2.* EXCLUDING(col1) FROM ...

I think changing the "indirection_el" rule might have an impact to
OLD/NEW, but I'm not sure. Is it legal to write OLD.*, and if so,
would you also want to write OLD.* EXCLUDING (...) in those cases? I
think this only applies to RULES or SQL-type trigger functions, but
not pl/pgsql?

Then it looks like touching various functions in src/backend/nodes/*.c
to do the right things with the new exclusion list field in A_Star. I
haven't traced through everything yet, but it looks like if the
various places in src/backend/nodes/*.c are done correctly, then
regurgitating a view definition or whatnot that includes this syntax
will be automatic (i.e., no specific support required for pg_dump)?

Anyways, at first I thought it would be about 8hrs of work just to get
something working. Maybe it's more like 20, but even still, it seems
fairly straightforward.

eric

David Wilson wrote:
> On Sun, Oct 30, 2011 at 1:10 AM, Darren Duncan <darren(at)darrenduncan(dot)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

On Sun, Oct 30, 2011 at 3:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> It's entirely possible that it will get bounced on standards-compliance
> grounds.

And that's a perfectly valid reason to reject it.

> In particular, I don't think it's acceptable to introduce a
> new reserved keyword for this --- that would fall under the "fails to
> not break anything else" category.

Please correct me if I'm wrong, but if we choose the word carefully
(which is why I chose EXCLUDING), I think we're okay? EXCLUDING is
already defined as an "ordinary key word". And it's new use in this
situation seems to be completely unambiguous, such that you'd still be
able to use "excluding" everywhere you already could.

You know more about the grammar than I (or probably most anyone), so
I'm wondering why you think it might need to be a "reserved keyword"?
Alternatively, would it be okay to use an existing reserved keyword?

eric

Tom Lane wrote:
> Darren Duncan <darren(at)darrenduncan(dot)net> writes:
>> The real question to ask ourselves is, if Eric Ridge is willing to do all the
>> work to implement this feature, and the code quality is up to the community
>> standards and doesn't break anything else, then will the code be accepted?
>
> It's entirely possible that it will get bounced on standards-compliance
> grounds. In particular, I don't think it's acceptable to introduce a
> new reserved keyword for this --- that would fall under the "fails to
> not break anything else" category.
>
> regards, tom lane

Well then we come up with a (SQL-level) syntax for the feature that doesn't
introduce new reserved keywords.

As I said before, the important thing is to have the feature, and that the exact
syntax is the main point to discuss.

Postgres already has a number of syntactic features that aren't in the SQL
standard and coexist, and so we add one of those.

-- Darren Duncan

Stupid question:

Is this just a display thing? Or does this have impact for things such
as COUNT(*) vs COUNT(1)?

Is it "like a view, but on the fly"?

I'm found myself in the *occasional* (certainly not daily!) situation
where such a feature might be useful, but each time I wonder about if
there should be a better way, I realize that if I ever saw such a thing
in production code it would be the first code I shot down. 1) Not
standards compliant, 2) Not deterministic (i.e. a database change might
cause my code to break), 3) Working around a problem that maybe
shouldn't exist in the first place? It's a like buying a rug, so that
nobody sees the scratches on the floor.

I can see the contention. :-)

If it existed, I would occasionally use it from the command line. I'm
thinking three times a year. Certainly not daily. Heck, if it's more
characters to type (than "select * ...") the number of times I would
bother typing it are quite short. :-)

Cheers,

--
Mark Mielke<mark(at)mielke(dot)cc>

On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)cc> wrote:
> Stupid question:
>
> Is this just a display thing?

Well, it's a "display thing" as much as any SELECT statement
(especially via psql) is a "display thing". It's more like "I want
all 127 columns, except the giant ::xml column, and I'm too lazy to
type each column name out by hand".

> Or does this have impact for things such as COUNT(*) vs COUNT(1)?

If it does, it's broken.

> Is it "like a view, but on the fly"?

Naw, it's just short-hand for omitting columns from the output target
list. As I'm envisioning the feature, it would be SQL-level syntax,
so you could bake it into a view, but...

> 1) Not standards compliant,

Sure, no doubt. I think the "::typename" syntax is not standards
compliant either, and I bet I'm not the only Postgres user to use that
every day. But I secretly tend to agree, I wouldn't necessarily want
to use this in production/application code.

> 2) Not deterministic (i.e. a database change might cause my code to break),

Okay, I'm inventing a use-case here, but say you have a "users" table
with various bits of metadata about the user, including password.
Maybe, regardless of database changes, you never want the password
column returned: SELECT * EXCLUDING (password) FROM tbl_users;

Changes of omission can break your code just as easily.

> 3) Working around a problem that maybe shouldn't exist in the first place? It's
> a like buying a rug, so that nobody sees the scratches on the floor.

Sometimes, rugs are cheaper than new floors.

eric

Eric Ridge <eebbrr(at)gmail(dot)com> writes:
> On Sun, Oct 30, 2011 at 3:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> In particular, I don't think it's acceptable to introduce a
>> new reserved keyword for this --- that would fall under the "fails to
>> not break anything else" category.

> Please correct me if I'm wrong, but if we choose the word carefully
> (which is why I chose EXCLUDING), I think we're okay? EXCLUDING is
> already defined as an "ordinary key word".

Yeah, it's unreserved so it doesn't break use of the same name for
columns or functions. I'm not sure that you can make the syntax work
the way you suggest without bumping up its reserved-ness level.
That's just a gut feeling, I've not tried it ... but the proposed
syntax sure looks a lot like a call to a function named EXCLUDING.

regards, tom lane

Eric Ridge <eebbrr(at)gmail(dot)com> writes:
> On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)cc> wrote:
>> 2) Not deterministic (i.e. a database change might cause my code to break),

> Okay, I'm inventing a use-case here, but say you have a "users" table
> with various bits of metadata about the user, including password.
> Maybe, regardless of database changes, you never want the password
> column returned: SELECT * EXCLUDING (password) FROM tbl_users;

Well, here you're not only inventing a use-case, but you're making a lot
of contrary-to-fact-and-to-SQL-spec assumptions about when the * notation
gets expanded. This thing wouldn't be useful that way in views.

regards, tom lane

On Sun, Oct 30, 2011 at 4:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> That's just a gut feeling, I've not tried it ... but the proposed
> syntax sure looks a lot like a call to a function named EXCLUDING.

I think what makes it okay is that its new use is only defined to
immediately follow an asterisk in the "target_el" production. If you
look at gram.y:11578 (from git HEAD), I was thinking this:
| a_expr
{
$$ = makeNode(ResTarget);
$$->name = NULL;
$$->indirection = NIL;
$$->val = (Node *)$1;
$$->location = @1;
}
+ | '*' EXCLUDING '(' columnref_list ')'
+ {
+ /** make magic happen */
+ }
| '*'
{
ColumnRef *n = makeNode(ColumnRef);
n->fields =
list_make1(makeNode(A_Star));
n->location = @1;

$$ = makeNode(ResTarget);
$$->name = NULL;
$$->indirection = NIL;
$$->val = (Node *)n;
$$->location = @1;
}

And it looks like something similar would be necessary in the
"indirection_el" production, around line 11478. But that might be
overly simplistic (and wrong).

eric

On 10/30/2011 04:03 PM, Tom Lane wrote:
>
>> Please correct me if I'm wrong, but if we choose the word carefully
>> (which is why I chose EXCLUDING), I think we're okay? EXCLUDING is
>> already defined as an "ordinary key word".
> Yeah, it's unreserved so it doesn't break use of the same name for
> columns or functions. I'm not sure that you can make the syntax work
> the way you suggest without bumping up its reserved-ness level.
> That's just a gut feeling, I've not tried it ... but the proposed
> syntax sure looks a lot like a call to a function named EXCLUDING.

Adding this rule doesn't appear to cause any complications:

target_el: '*' EXCLUDING '(' ')'

I'm not saying we need to do this, although there have been times when I
might have liked it, but I suspect it would not cause us any grammar
problems at least.

cheers

andrew

Eric Ridge wrote:
> On Sun, Oct 30, 2011 at 4:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> That's just a gut feeling, I've not tried it ... but the proposed
>> syntax sure looks a lot like a call to a function named EXCLUDING.
>
> I think what makes it okay is that its new use is only defined to
> immediately follow an asterisk in the "target_el" production. If you
> look at gram.y:11578 (from git HEAD), I was thinking this:
<snip>

That's an interesting thought.

I had previously envisioned EXCLUDING to appear exactly once in the SELECT list,
where it functions as a column analogy to EXCEPT for rows. So you'd say:

SELECT <list of stuff to keep> EXCLUDING <list of stuff to skip> FROM ...

But then your comment makes me think that your EXCLUDING actually could have a
much tighter scope, and as a result might appear multiple times, like AS can:

SELECT foo.* EXCLUDING foo.x, bar.* EXCLUDING bar.y, baz.z, (a+b) AS c FROM ...

So if you do this, EXCLUDING has syntactic precedence similar to AS, and while
AS is only valid not following a *, EXCLUDING is only valid following a *, and
so EXCLUDING directly modifies a single * and not the SELECT list in general.

Is that where you're going with this?

If so, I think that would make the feature even more valuable and more
syntactically clean than I had previously thought.

-- Darren Duncan

On Sun, Oct 30, 2011 at 4:43 PM, Darren Duncan <darren(at)darrenduncan(dot)net> wrote:

>  SELECT foo.* EXCLUDING foo.x, bar.* EXCLUDING bar.y, baz.z, (a+b) AS c FROM
> ...
> Is that where you're going with this?

Yes. It's basically a modifier to the star that immediately precedes
it. In order to support excluding multiple columns, it needs parens:
SELECT foo.* EXCLUDING (foo.x, foo.y), bar.* EXCLUDING (bar.y),
baz.z, (a+b) AS c

but yes, that's what I'm thinking. I think doing this will require
more changes to the grammar than I had first thought because there'd
be no point in supporting:
SELECT foo.* EXCLUDING (foo.* EXCLUDING foo.y) FROM ...
It looks like the above would be implicitly allowed without a bit of extra work.

But, if you've got a complex query consisting of a few joins, it'd be
nice to say:
SELECT * EXCLUDING (table1.*, table2.x) FROM table1 INNER JOIN table2 ...

> If so, I think that would make the feature even more valuable and more
> syntactically clean than I had previously thought.

I don't actually like the term "EXCLUDING", but it conveys what's
happening and is already defined as a keyword. I thought about
"EXCEPT", but that doesn't work for obvious reasons, and "NOT" might
just be confusing.

eric

Eric Ridge wrote:
> I don't actually like the term "EXCLUDING", but it conveys what's
> happening and is already defined as a keyword. I thought about
> "EXCEPT", but that doesn't work for obvious reasons, and "NOT" might
> just be confusing.

How about "BUT"?

Is that already in use by something? Its nice and short and conveys the
"except" meaning.

And there is already precedent for using that word for this purpose.

CJ Date already uses "ALL BUT" in his literature as a modifier to his
illustrative relation projection syntax to give the complementary projection,
like with "r{x,y}" vs "r{all but x,y}".

Also, a more tenuous connection, Larry Wall likes "but" as logical modifier.

-- Darren Duncan

On Sun, Oct 30, 2011 at 3:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
<snip>

> "fails to not break anything else" category.

From what I've seen watching this list, you're usually right. :)

It looks like it's perfectly okay to write:
SELECT pg_class.* AS foo FROM pg_class;
(with or without the AS)

I don't know what the above actually means, but it stops SELECT
pg_class.* EXCLUDING(...) dead in its tracks. So, I'd have to break a
syntax (albeit silly?) that currently works to support this. :(

eric

On 10/30/2011 03:50 PM, Eric Ridge wrote:
> On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielke<mark(at)mark(dot)mielke(dot)cc> wrote:
>> 2) Not deterministic (i.e. a database change might cause my code to break),
> Okay, I'm inventing a use-case here, but say you have a "users" table
> with various bits of metadata about the user, including password.
> Maybe, regardless of database changes, you never want the password
> column returned: SELECT * EXCLUDING (password) FROM tbl_users;
>
> Changes of omission can break your code just as easily.

I think I wasn't as clear as I intended. In many ways, I think use of
"*" in the first place is wrong for code (despite that I do it as well).
Therefore, "* EXCLUDING (...)" would also be wrong. It comes to "does
the code know what it wants?"

In the above case - maybe you don't want password - what about social
insurance number, credit card number, or any other private bit? The only
way to truly know you aren't accidentally pulling in fields you don't
need or want to unnecessarily expose on the wire - is to specifically
list the fields you DO want, which is precisely to not use "*" at all.

A particular area that I don't like "*" is that my code may make an
assumption about the exact field names, or the field order that comes
out. If this is explicitly specified, then it will survive "ALTER
TABLE", or a restore of the table with columns in a different order, or
a replacement of the table with a view. However, if I use "*", then my
code is likely to fail in any of these cases, and possibly fail in some
unexpected way. For example, in a language such as Perl with DBI
returning a hash, I may accidentally assume that the field is always
undef. It might even pass some designer testing if the value is usually
NULL = undef, and I fail to simulate the case where it is not.

"select *" is not deterministic from a programming perspective.

--
Mark Mielke<mark(at)mielke(dot)cc>

On Sun, Oct 30, 2011 at 7:46 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)cc> wrote:
> In the above case - maybe you don't want password - what about social
> insurance number, credit card number, or any other private bit? The only way
> to truly know you aren't accidentally pulling in fields you don't need or
> want to unnecessarily expose on the wire - is to specifically list the
> fields you DO want, which is precisely to not use "*" at all.

A fairly useful characteristic case would be related to this, namely...

select * excluding password from some_table;

The sensitive bits of data are often fairly well enumerated, so it's a
useful-ish idea to pull everything except that.

The joys and challenges may be pretty well characterized by:

create view sanitized_table as
select * excluding password from some_table;

Now, if someone running queries somewhere downstream is sufficiently
lacking in clue that they don't know which columns they *do* want,
then it seems dubious to me that letting them exclude the columns that
they imagine that they know they *don't* want is actually valid.

That is, if they don't know what they want (e.g. - the column set in a
traditional sans-EXCLUDES query), then I'm rather suspicious that they
aren't competent to build a proper EXCLUDES clause.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

Mark Mielke wrote:
> On 10/30/2011 03:50 PM, Eric Ridge wrote:
>> Changes of omission can break your code just as easily.
>
> I think I wasn't as clear as I intended. In many ways, I think use of
> "*" in the first place is wrong for code (despite that I do it as well).
> Therefore, "* EXCLUDING (...)" would also be wrong. It comes to "does
> the code know what it wants?"
<snip>
>
> "select *" is not deterministic from a programming perspective.

I understand what you're saying. However, we're stuck with * because it is in
the standard and is widely used, and if we have * anyway, then the exclusion
proposal is just an enhancement to that. So there is no reason to reject the
complementary columns feature because of the problems with "select *"; you might
as well argue to get rid of "select *". -- Darren Duncan

On Sun, Oct 30, 2011 at 6:11 PM, Darren Duncan <darren(at)darrenduncan(dot)net> wrote:
> Eric Ridge wrote:
>>
>> I don't actually like the term "EXCLUDING", but it conveys what's
>> happening and is already defined as a keyword.  I thought about
>> "EXCEPT", but that doesn't work for obvious reasons, and "NOT" might
>> just be confusing.
>
> How about "BUT"?
>
> Is that already in use by something?  Its nice and short and conveys the
> "except" meaning.
>
> And there is already precedent for using that word for this purpose.
>
> CJ Date already uses "ALL BUT" in his literature as a modifier to his
> illustrative relation projection syntax to give the complementary
> projection, like with "r{x,y}" vs "r{all but x,y}".
>
> Also, a more tenuous connection, Larry Wall likes "but" as logical modifier.

Look, there's no good speculating about what might work without
sitting down and editing gram.y. The exact choice of keyword matters
a lot less than whether this can be done with out shift/reduce or
reduce/reduce conflicts.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> The exact choice of keyword matters
> a lot less than whether this can be done with out shift/reduce or
> reduce/reduce conflicts.

Which is the problem right now. See my other email.

I'll post a diff tomorrow. Maybe if enough folks think is a feature worth having we can find a solution. My gram.y-fu is at its limits right now.

eric

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.

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."

2011/10/31 Christopher Browne <cbbrowne(at)gmail(dot)com>:
> 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.
>
> 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 :
>

+1

It can carry some new problems with cache - actually we don't need
rebuild views after additing column to table or view

Pavel

> "No, we're not changing policy.  The fact that PGDG added this to 9.2 does
> *not* imply our policy was wrong."

On Sun, Oct 30, 2011 at 8:50 PM, Eric Ridge <eebbrr(at)gmail(dot)com> wrote:
> Well, it's a "display thing" as much as any SELECT statement
> (especially via psql) is a "display thing".  It's more like "I want
> all 127 columns, except the giant ::xml column, and I'm too lazy to
> type each column name out by hand".
>

How about an option for psql to truncate too long columns to X characters ?

Greetings
Marcin Mańk

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

On 10/31/2011 06:54 AM, Marcin Mańk wrote:
> On Sun, Oct 30, 2011 at 8:50 PM, Eric Ridge<eebbrr(at)gmail(dot)com> wrote:
>> Well, it's a "display thing" as much as any SELECT statement
>> (especially via psql) is a "display thing". It's more like "I want
>> all 127 columns, except the giant ::xml column, and I'm too lazy to
>> type each column name out by hand".
>>
> How about an option for psql to truncate too long columns to X characters ?

I would use this option frequently... :-)

It seems more to the point of what is trying to be accomplished, and
doesn't even require a server change? :-)

--
Mark Mielke<mark(at)mielke(dot)cc>

On Mon, Oct 31, 2011 at 12:54, Marcin Mańk <marcin(dot)mank(at)gmail(dot)com> wrote:
> How about an option for psql to truncate too long columns to X characters ?

I would really want this in some form or another; for example, being
able to hide bytea values entirely, or set limits to how many
characters are displayed for fields.

Unfortunately it's far less efficient. Fields would be truncated in
psql, so full values are still detoasted and transmitted over the
network.

Regards,
Marti

On 1 November 2011 00:14, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> On 10/30/2011 10:00 PM, Christopher Browne wrote:
>> 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.

I couldn't agree more with Andrew's comment. What's good for an ad
hoc psql query isn't congruent with what's good for your application
queries.

We could have " * EXCLUDING " and still say that it is undesirable in
all the same contexts that " * " is undesirable.

Cheers,
BJ

On Sat, Oct 29, 2011 at 5:26 PM, Eric Ridge <eebbrr(at)gmail(dot)com> wrote:
> Would y'all accept a patch that extended the "SELECT *" syntax to let
> you list fields to exclude from the A_Star?
>
> Quite regularly I'll be testing queries via psql and want to see all
> the columns from a fairly wide table except maybe a giant text or xml
> column.  A syntax like:
>
>     SELECT * EXCLUDING (big_col1, big_col2) FROM foo;
>
> would be pretty handy.  It would definitely save some typing in
> certain cases.  It seems like such a syntax would better document the
> intent of a query too, rather than leaving one wondering if "big_col1"
> was supposed to be omitted from the target list or not.
>
> Anyways, I just wanted to run the idea by youse guys before I put too
> much more effort into it.  I've already made what appear to be the
> minimum necessary changes to gram.y, and a few quick greps through the
> code make me think the rest will be pretty easy.
>
> Maybe the SQL spec says something about this and nobody's done the work yet?
>
> Thanks for your input!

FWIW, this seems to come up all the time for me and I've often
wondered about something like this. Just be advised that the bar for
syntax extensions is very high because they can burn you down the line
quite easily.

merlin

* Marti Raudsepp (marti(at)juffo(dot)org) wrote:
> Unfortunately it's far less efficient. Fields would be truncated in
> psql, so full values are still detoasted and transmitted over the
> network.

I'm thinking that we're not too worried about performance of ad-hoc
psql queries..? At least, for the queries that I'd use this for, I
wouldn't be worried about that.

The various syntax proposals do seem overly long for this, however.. I
was just wondering about something like:

select ~* blah, blah, blah FROM ...

Strikes me as pretty unlikely that making a new 'version' of * like this
is going to break anything or be broken by the SQL standard. Note- I
haven't looked at the * production or tried to do anything w/ gram.y to
support this yet, but it's a heck of a lot shorter..

Thanks,

Stephen

2011/11/1 Stephen Frost <sfrost(at)snowman(dot)net>:
> * Marti Raudsepp (marti(at)juffo(dot)org) wrote:
>> Unfortunately it's far less efficient. Fields would be truncated in
>> psql, so full values are still detoasted and transmitted over the
>> network.
>
> I'm thinking that we're not too worried about performance of ad-hoc
> psql queries..?  At least, for the queries that I'd use this for, I
> wouldn't be worried about that.
>
> The various syntax proposals do seem overly long for this, however..  I
> was just wondering about something like:
>
> select ~* blah, blah, blah FROM ...
>
> Strikes me as pretty unlikely that making a new 'version' of * like this
> is going to break anything or be broken by the SQL standard.  Note- I
> haven't looked at the * production or tried to do anything w/ gram.y to
> support this yet, but it's a heck of a lot shorter..
>

some other idea - but only for psql

we can define a special values, that ensure a some necessary
preexecution alchemy with entered query

\pset star_exclude_names col1, col2, col3
\pset star_exclude_types xml, bytea, text(unlimited)

select * from ...

1) get result descriptor from query
2) update a query to SELECT expected_columns FROM (original query) x
3) eval new query

point1 1,2,3 will be transparent for user and it has not any performance issue

Regards

Pavel

>        Thanks,
>
>                Stephen
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEARECAAYFAk6wGEsACgkQrzgMPqB3kii8fwCeM0TR8eeVsETjs8TEfi04mDvs
> nwEAoJ1I58IGUdRjJvRZPBaYLkQF3PiF
> =BPha
> -----END PGP SIGNATURE-----
>
>

On Tue, Nov 1, 2011 at 12:03 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>  Note- I haven't looked at the * production or tried to do anything w/ gram.y to
> support this yet, but it's a heck of a lot shorter..

My original thought, that I probably didn't explain too clearly, was
to make the "EXCLUDING (...)" bit a modifier to the A_Star node. The
idea being that you could write "* EXCLUDING (...)" anywhere you can
currently write "*".

It's dead simple for the case of:
SELECT * FROM ...
but because of the "indirection" productions in gram.y, it's literally
impossible for:
SELECT tablename.* FROM ...
without possibly breaking existing queries.

Nonetheless, even if it were only available for the first form, it
would be very useful. For the ad-hoc type stuff I do, it'd still be
great to write:
SELECT * EXCLUDING (x.a, x.b, x.c) FROM (SELECT .... x);

I've already got gram.y working the way it needs to, and I've started
to get the exclusion list passed into the places it needs to go.

If y'all would be willing to accept it in this "limited" form, I'll
continue to work on it.

eric

On Tue, Nov 1, 2011 at 12:24 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> some other idea - but only for psql
>
> we can define a special values, that ensure a some necessary
> preexecution alchemy with entered query
>
> \pset star_exclude_names col1, col2, col3
> \pset star_exclude_types xml, bytea, text(unlimited)
>

Sure, something like that could be useful too. It might be confusing
to users if they forget that they set an exclusion list, but there's
probably ways to work around that.

However, the nice thing about the feature being in SQL is that you can
use it from all clients, and even in other useful ways. COPY would be
an example (something I also do frequently):

COPY (SELECT * EXCLUDING (a, b, c) FROM <big query>) TO 'somefile.csv' WITH CSV;

Right now, if you want to exclude a column, you have to list all the
others out manually, or just dump everything and deal with it in an
external tool.

I generally agree with everyone that says using this in application
code is a bad idea, but I don't think that's reason alone to reject
the idea on its face.

eric

2011/11/1 Eric Ridge <eebbrr(at)gmail(dot)com>:
> On Tue, Nov 1, 2011 at 12:24 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> some other idea - but only for psql
>>
>> we can define a special values, that ensure a some necessary
>> preexecution alchemy with entered query
>>
>> \pset star_exclude_names col1, col2, col3
>> \pset star_exclude_types xml, bytea, text(unlimited)
>>
>
> Sure, something like that could be useful too.  It might be confusing
> to users if they forget that they set an exclusion list, but there's
> probably ways to work around that.
>
> However, the nice thing about the feature being in SQL is that you can
> use it from all clients, and even in other useful ways.  COPY would be
> an example (something I also do frequently):
>
> COPY (SELECT * EXCLUDING (a, b, c) FROM <big query>) TO 'somefile.csv' WITH CSV;
>
> Right now, if you want to exclude a column, you have to list all the
> others out manually, or just dump everything and deal with it in an
> external tool.
>
> I generally agree with everyone that says using this in application
> code is a bad idea, but I don't think that's reason alone to reject
> the idea on its face.
>
> eric
>

2011/11/1 Eric Ridge <eebbrr(at)gmail(dot)com>:
> On Tue, Nov 1, 2011 at 12:24 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> some other idea - but only for psql
>>
>> we can define a special values, that ensure a some necessary
>> preexecution alchemy with entered query
>>
>> \pset star_exclude_names col1, col2, col3
>> \pset star_exclude_types xml, bytea, text(unlimited)
>>
>
> Sure, something like that could be useful too.  It might be confusing
> to users if they forget that they set an exclusion list, but there's
> probably ways to work around that.
>
> However, the nice thing about the feature being in SQL is that you can
> use it from all clients, and even in other useful ways.  COPY would be
> an example (something I also do frequently):
>
> COPY (SELECT * EXCLUDING (a, b, c) FROM <big query>) TO 'somefile.csv' WITH CSV;
>
> Right now, if you want to exclude a column, you have to list all the
> others out manually, or just dump everything and deal with it in an
> external tool.
>

sorry, I don't accept it. I am able to understand your request for
adhoc queries. But not for COPY.

and if you need it - you can write C function.

> I generally agree with everyone that says using this in application
> code is a bad idea, but I don't think that's reason alone to reject
> the idea on its face.

I can accept a PostgreSQL extensions if there are no other way how do
it effective. But it is not this case.

>
> eric
>

On Tue, Nov 1, 2011 at 1:33 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> COPY (SELECT * EXCLUDING (a, b, c) FROM <big query>) TO 'somefile.csv' WITH CSV;
>>
> sorry, I don't accept it. I am able to understand your request for
> adhoc queries. But not for COPY.

I apologize if that example was confusing. I wasn't also suggesting
expanding COPY's syntax. I was merely pointing out that if
"EXCLUDING(…)" were implemented, you'd be able to use it within the
query given to the COPY command.

eric

ps, it seems my messages aren't hitting the list? weird.

2011/11/1 Eric Ridge <eebbrr(at)gmail(dot)com>:
> On Tue, Nov 1, 2011 at 1:33 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>> COPY (SELECT * EXCLUDING (a, b, c) FROM <big query>) TO 'somefile.csv' WITH CSV;
>>>
>> sorry, I don't accept it. I am able to understand your request for
>> adhoc queries. But not for COPY.
>
> I apologize if that example was confusing.  I wasn't also suggesting
> expanding COPY's syntax.  I was merely pointing out that if
> "EXCLUDING(…)" were implemented, you'd be able to use it within the
> query given to the COPY command.

I understand it, it is really bad idea use a star in export queries

Pavel

>
> eric
>
> ps, it seems my messages aren't hitting the list?  weird.
>

On Mon, Oct 31, 2011 at 09:14:48AM -0400, Andrew Dunstan wrote:
> 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.
>

As I do the ad hoc query thing more than I'd like to admit, I think
there's a place for some form of negation for *. A workaround similar to
what you describe here would be to add special tab completion to psql
that would expand * to the full list (probably on double tab ...)

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE

Eric Ridge <eebbrr(at)gmail(dot)com> writes:
> My original thought, that I probably didn't explain too clearly, was
> to make the "EXCLUDING (...)" bit a modifier to the A_Star node. The
> idea being that you could write "* EXCLUDING (...)" anywhere you can
> currently write "*".

I can think of a number of places where you can write "*" where I'm
pretty sure we *don't* want this. It should be restricted to top-level
entries in SELECT targetlists, IMO.

regards, tom lane

On Nov 1, 2011, at 6:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I can think of a number of places where you can write "*" where I'm
> pretty sure we *don't* want this. It should be restricted to top-level
> entries in SELECT targetlists, IMO.

Yes. That is the exact conclusion I've come to.

However, why is

select table.* foo from table

allowed? What does that even mean?

eric

"Eric B. Ridge" <eebbrr(at)gmail(dot)com> writes:
> However, why is
> select table.* foo from table
> allowed? What does that even mean?

Doesn't mean anything, I think --- the SQL standard seems to exclude it.
It's fairly hard to prevent it at the grammar level, since we regard
"foo.*" as a type of primitive expression, but I suspect it might be a
good idea for transformTargetList to throw an error instead of silently
ignoring the column label.

regards, tom lane

On Sun, Oct 30, 2011 at 22:12, Eric Ridge <eebbrr(at)gmail(dot)com> wrote:

>
> Yes. It's basically a modifier to the star that immediately precedes
> it. In order to support excluding multiple columns, it needs parens:
> SELECT foo.* EXCLUDING (foo.x, foo.y), bar.* EXCLUDING (bar.y),
> baz.z, (a+b) AS c
>
> but yes, that's what I'm thinking. I think doing this will require
> more changes to the grammar than I had first thought because there'd
> be no point in supporting:
> SELECT foo.* EXCLUDING (foo.* EXCLUDING foo.y) FROM ...
> It looks like the above would be implicitly allowed without a bit of extra
> work.
>
> But, if you've got a complex query consisting of a few joins, it'd be
> nice to say:
> SELECT * EXCLUDING (table1.*, table2.x) FROM table1 INNER JOIN table2
> ...
>
>
Putting aside arguments like "it is not a good idea to use * because it
generates not sustainable code especially in case when you extend table
structure", I think this construct would be really nice for building ROWs,
for example in plpgsql triggers or in conditions for big update statements:

IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.* EXCLUDING (
last_modified ) ) THEN NEW.last_modified = clock_timestamp() ; END IF

by now, I do not know any really nice syntax to do that efficiently, and
for some wide tables, building this kind of structures listing all the
fields, that you have there is completely stupid and makes code unreadable.

So I would definitely like to have a syntax, that you are suggesting in
case it would give a possibility to construct ROWs (RECORDs).

Regards,

-- Valentine Gogichashvili

* Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
> I understand it, it is really bad idea use a star in export queries

It's not how I'd want to automate things, but I hardly see it being a
"really bad idea" for ad-hoc COPY usage..

Stephen

On 11/02/2011 03:16 AM, Valentine Gogichashvili wrote:
>
> Putting aside arguments like "it is not a good idea to use * because
> it generates not sustainable code especially in case when you extend
> table structure", I think this construct would be really nice for
> building ROWs, for example in plpgsql triggers or in conditions for
> big update statements:
>
> IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.*
> EXCLUDING ( last_modified ) ) THEN NEW.last_modified =
> clock_timestamp() ; END IF
>
>

That's a very good use case. I could certainly have used this in the past.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 11/02/2011 03:16 AM, Valentine Gogichashvili wrote:
>> Putting aside arguments like "it is not a good idea to use * because
>> it generates not sustainable code especially in case when you extend
>> table structure", I think this construct would be really nice for
>> building ROWs, for example in plpgsql triggers or in conditions for
>> big update statements:
>>
>> IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.*
>> EXCLUDING ( last_modified ) ) THEN NEW.last_modified =
>> clock_timestamp() ; END IF

> That's a very good use case. I could certainly have used this in the past.

Well ... this is inventing use cases that have nothing to do with the
proposed feature and are entirely incapable of being supported by the
proposed implementation. And I'm not sure why we should put aside the
argument that this is only a good idea in ad-hoc queries, either.

regards, tom lane

On 11/02/2011 09:56 AM, Tom Lane wrote:
> Andrew Dunstan<andrew(at)dunslane(dot)net> writes:
>> On 11/02/2011 03:16 AM, Valentine Gogichashvili wrote:
>>> Putting aside arguments like "it is not a good idea to use * because
>>> it generates not sustainable code especially in case when you extend
>>> table structure", I think this construct would be really nice for
>>> building ROWs, for example in plpgsql triggers or in conditions for
>>> big update statements:
>>>
>>> IF (NEW.* EXCLUDING ( last_modified ) ) IS DISTINCT FROM (OLD.*
>>> EXCLUDING ( last_modified ) ) THEN NEW.last_modified =
>>> clock_timestamp() ; END IF
>> That's a very good use case. I could certainly have used this in the past.
> Well ... this is inventing use cases that have nothing to do with the
> proposed feature and are entirely incapable of being supported by the
> proposed implementation. And I'm not sure why we should put aside the
> argument that this is only a good idea in ad-hoc queries, either.
>
>

Well, yes, you're right that it's not covered by the original feature. I
guess I got interested because a couple of years ago I had to write some
triggers in PLPerl and with much lower efficiency to achieve the same
effect as this.

cheers

andrew

On Tue, Nov 1, 2011 at 11:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Doesn't mean anything, I think --- the SQL standard seems to exclude it.
> It's fairly hard to prevent it at the grammar level, since we regard
> "foo.*" as a type of primitive expression, but I suspect it might be a
> good idea for transformTargetList to throw an error instead of silently
> ignoring the column label.

Let me take a stab at fixing it in the grammar... if it's even do-able.

If it can be fixed at the grammar, it'll get me a lot closer to doing
what I want to do with the actual feature.

eric