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?

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 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 , , , ;

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.

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.

Again, I wonder what EnterpriseDB does in this case?

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:

test=# select * from observation;
observation_id | record_id | score_id
----------------+-----------+----------
           3240 |         1 |        1
           3239 |         1 |        1
           3238 |         1 |        2
           3237 |         1 |        1
           2872 |         2 |        1
           2869 |         2 |        2
           2870 |         2 |        1
           2871 |         2 |        1
           3218 |         3 |        2
           3217 |         3 |        1
(10 rows)

test=# select record_id as foo, count(observation_id) from observation group by record_id;
foo | count
-----+-------
   3 |     2
   2 |     4
   1 |     4
(3 rows)

test=# select record_id as foo, count(observation_id) from observation group by foo;
foo | count
-----+-------
   3 |     2
   2 |     4
   1 |     4
(3 rows)

test=# select record_id as foo, count(observation_id) as bar from observation group by foo;
foo | bar
-----+-----
   3 |   2
   2 |   4
   1 |   4
(3 rows)

test=# select record_id as foo, count(observation_id) as bar from observation group by record_id;
foo | bar
-----+-----
   3 |   2
   2 |   4
   1 |   4
(3 rows)

test=# select version();
version ------------------------------------------------------------------------ ---------------------------------------------------------------------- PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5367)
(1 row)

I'm not getting an error in any permutation that I can think of. What am I missing?

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'm not trying to dismiss your points, just trying to address them. I'm interested to hear what others have to say.

Michael Glaesemann
grzm seespotcode net



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to