On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: > > On Aug 22, 2007, at 18:45 , Ben Tilly wrote: > > > 1. Just a minor annoyance, but why must subqueries in FROM clauses > > have an alias? > > It's required by the SQL standard, AIUI. I wonder what EnterpriseDB > does?
I can well believe that the standard says that you must accept subqueries with aliases. But does it say you must reject subqueries without aliases? I strongly doubt that. I have no clue what EnterpriseDB does. > > 2. Why is 'non-integer constant in GROUP BY' an error? > > > This works for now: > > > > case when true then true end > > > > but I don't know whether some future version of postgres might break > > my code by banning that as well. > > The PostgreSQL developers generally tries hard to preserve backwards > compatibility, so I doubt the case expression as you have it would go > away (though I'm kind of surprised it's allowed). Am I wrong in > thinking that Oracle would accept the same format PostgreSQL does? In The reason for my comparing to Oracle is that I used to work at an Oracle shop. I now work at a postgres shop. Portability is not my issue, just the annoyances that I experienced moving from one to the other. As for whether that case expression would go away, that it is allowed is such an obscure feature that I doubt anyone changing that code would notice if it was removed. > that case, couldn't you use whatever method works in PostgreSQL in > Oracle? I haven't checked the SQL standard, but it seems unlikely > it'd allow something like > > GROUP BY , , , ; That's not what Oracle accepts that postgres does not. What Oracle accepts is: ... GROUP BY 'foo'; > AIUI, Integers are only allowed because the SQL standard explicitly > allows you to refer to columns by the order they appear in the SELECT > list. Otherwise the GROUP BY items need to be column names. Need to be? The SQL-92 standard is clear that you must accept a list of column names. It is also clear that a column name must be be of the form field or table.field. In no way, shape or form does that allow having terms like trim(foo.bar) in a group by. But every reasonable database that I know - including postgres - allows that. The standard very wisely does not forbid extensions. Every database has extensions. In some cases, such as allowing trim(foo.bar) in a group by clause, some extensions are so common as to be a standard. (I don't have a copy of any later standards so I don't know whether that has since been explicitly allowed.) Therefore the real question is how much farther than the standard you go. Postgres explicitly disallows a constant character expression. But it allows the constant case expression that I gave. It would be nice for me to not have to remember that very obscure and convoluted case. > Both 1 and 2 seem to me to be places where Oracle is likely deviating > from the standard. If you're targeting Oracle, then using Oracle- > specific syntax might be warranted. If you're hoping to target more > than one possible backend, I'd think it be better to use more > portable syntax (e.g., SQL-standard syntax) than expecting other > DBMSs to follow another's deviations. That's not to say PostgreSQL > does not have non-standard syntax: in places, it does. But it does > try to hew very closely to the standard. The queries that I'm writing are not hoping to target more than one database at one company. > Again, I wonder what EnterpriseDB does in this case? No clue. > > 3. How hard would it be to have postgres ignore aliases in group by > > clauses? Per my comments above, I often build complex queries in > > code. I can't easily use the shortcut of referring to the select > > column by number because the position is hard to determine. So my > > code has to copy the select terms. But I can't copy them exactly > > because the select terms include lots of "...as foo" clauses that are > > not allowed in a group by. So I have to store very similar terms to > > use twice. > > Perhaps someone else knows what you're referring to here, but I'm > having a hard time without an example. Here's what I *think* you're > trying to say: What I'm trying to say is that it would be convenient for me to be able to write: select bar as "baz" , count(*) as "some count" from foo group by bar as "baz" That's not allowed right now because as is not allowed in a group by statement. [...] > > Which is to assume that > > a query without a group by clause, but with an aggregate function in > > the select, should have an implicit group by clause where you group by > > all non-aggregate functions in the select. > > > > For example > > > > SELECT foo, count(*) > > FROM bar > > > > would be processed as: > > > > SELECT foo, count(*) > > FROM bar > > GROUP BY foo > > It's been discussed before. I don't believe it's been rejected out of > hand (though you can check the archives), just that no one's gotten > around to it. (Don't know what the SQL-spec says on this point.) I don't know what the SQL spec says, but I know (having talked to other developers) that many people would find it very nice. > I'm not trying to dismiss your points, just trying to address them. > I'm interested to hear what others have to say. Cheers, Ben ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org