Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Valentine Gogichashvili
On Sun, Oct 30, 2011 at 22:12, Eric Ridge eeb...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Stephen Frost
* Pavel Stehule (pavel.steh...@gmail.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 signature.asc Description: Digital signature

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Andrew Dunstan
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Tom Lane
Andrew Dunstan and...@dunslane.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Andrew Dunstan
On 11/02/2011 09:56 AM, Tom Lane wrote: Andrew Dunstanand...@dunslane.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,

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-02 Thread Eric Ridge
On Tue, Nov 1, 2011 at 11:47 PM, Tom Lane t...@sss.pgh.pa.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Marti Raudsepp
On Mon, Oct 31, 2011 at 12:54, Marcin Mańk marcin.m...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Brendan Jurd
On 1 November 2011 00:14, Andrew Dunstan and...@dunslane.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Merlin Moncure
On Sat, Oct 29, 2011 at 5:26 PM, Eric Ridge eeb...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Pavel Stehule
2011/11/1 Eric Ridge eeb...@gmail.com: On Tue, Nov 1, 2011 at 12:24 PM, Pavel Stehule pavel.steh...@gmail.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,

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Pavel Stehule
2011/11/1 Eric Ridge eeb...@gmail.com: On Tue, Nov 1, 2011 at 12:24 PM, Pavel Stehule pavel.steh...@gmail.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,

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Eric Ridge
On Tue, Nov 1, 2011 at 12:03 PM, Stephen Frost sfr...@snowman.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Ross J. Reedstrom
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Tom Lane
Eric Ridge eeb...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Eric B. Ridge
On Nov 1, 2011, at 6:47 PM, Tom Lane t...@sss.pgh.pa.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.

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Pavel Stehule
2011/11/1 Eric Ridge eeb...@gmail.com: On Tue, Nov 1, 2011 at 1:33 PM, Pavel Stehule pavel.steh...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Eric Ridge
On Tue, Nov 1, 2011 at 12:24 PM, Pavel Stehule pavel.steh...@gmail.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,

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Eric Ridge
On Tue, Nov 1, 2011 at 1:33 PM, Pavel Stehule pavel.steh...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Stephen Frost
* Marti Raudsepp (ma...@juffo.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-11-01 Thread Tom Lane
Eric B. Ridge eeb...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-31 Thread Marcin Mańk
On Sun, Oct 30, 2011 at 8:50 PM, Eric Ridge eeb...@gmail.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.

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-31 Thread Andrew Dunstan
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-31 Thread Mark Mielke
On 10/31/2011 06:54 AM, Marcin Mańk wrote: On Sun, Oct 30, 2011 at 8:50 PM, Eric Ridgeeeb...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan
Pavel Stehule wrote: 2011/10/30 Darren Duncan dar...@darrenduncan.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread David Wilson
On Sun, Oct 30, 2011 at 1:10 AM, Darren Duncan dar...@darrenduncan.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 1:51 PM, Eric B. Ridge e...@tcdi.com wrote: eric PROPRIETARY AND COMPANY CONFIDENTIAL COMMUNICATIONS snip my bad. Switched email accounts without realizing. :( eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Jim Nasby
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric B. Ridge
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Robert Haas
On Sun, Oct 30, 2011 at 2:15 PM, Jim Nasby j...@nasby.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread David E. Wheeler
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Tom Lane
Darren Duncan dar...@darrenduncan.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 2:54 PM, Robert Haas robertmh...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan
David Wilson wrote: On Sun, Oct 30, 2011 at 1:10 AM, Darren Duncan dar...@darrenduncan.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 3:17 PM, Tom Lane t...@sss.pgh.pa.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan
Tom Lane wrote: Darren Duncan dar...@darrenduncan.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?

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Mark Mielke
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielke m...@mark.mielke.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Tom Lane
Eric Ridge eeb...@gmail.com writes: On Sun, Oct 30, 2011 at 3:17 PM, Tom Lane t...@sss.pgh.pa.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Tom Lane
Eric Ridge eeb...@gmail.com writes: On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielke m...@mark.mielke.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 4:03 PM, Tom Lane t...@sss.pgh.pa.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Andrew Dunstan
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan
Eric Ridge wrote: On Sun, Oct 30, 2011 at 4:03 PM, Tom Lane t...@sss.pgh.pa.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 4:43 PM, Darren Duncan dar...@darrenduncan.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric Ridge
On Sun, Oct 30, 2011 at 3:17 PM, Tom Lane t...@sss.pgh.pa.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)

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Mark Mielke
On 10/30/2011 03:50 PM, Eric Ridge wrote: On Sun, Oct 30, 2011 at 3:38 PM, Mark Mielkem...@mark.mielke.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Christopher Browne
On Sun, Oct 30, 2011 at 7:46 PM, Mark Mielke m...@mark.mielke.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Darren Duncan
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 (...)

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Robert Haas
On Sun, Oct 30, 2011 at 6:11 PM, Darren Duncan dar...@darrenduncan.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Eric B. Ridge
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Christopher Browne
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-30 Thread Pavel Stehule
2011/10/31 Christopher Browne cbbro...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-29 Thread Stephen Frost
Eric, * Eric Ridge (eeb...@gmail.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-29 Thread Eric Ridge
On Sat, Oct 29, 2011 at 6:35 PM, Stephen Frost sfr...@snowman.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-29 Thread Darren Duncan
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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-29 Thread Pavel Stehule
2011/10/30 Darren Duncan dar...@darrenduncan.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

Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?

2011-10-29 Thread Joshua D. Drake
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