Thread: PgSQL 6.5.1 and Group BY pb
Running PostgreSQL-6.5.1 (rpm version distributed for linux on redhat
6.0)
GROUP BY complitely fails
bash$ psql template1
template1=> CREATE TABLE "gruppi" ("id" int4 NOT NULL,"nome" character
varying(32));
CREATE
template1=> CREATE TABLE "usergroup" ("id" int4 NOT NULL,"uid" int4 NOT
NULL,"gid" int4 NOT NULL);
CREATE
template1=> insert into gruppi values (0,'ciao');
INSERT 31637 1
template1=> insert into gruppi values (1,'pippo');
INSERT 31638 1
template1=> insert into usergroup values (0,1,2);
INSERT 31639 1
template1=> insert into usergroup values (0,2,3);
INSERT 31640 1
template1=> insert into usergroup values (1,2,3);
INSERT 31641 1
And now the output query with GROUP BY:
template1=> SELECT g.nome,u.uid,u.id FROM gruppi g, usergroup u GROUP BY
g.nome;
ERROR: Illegal use of aggregates or non-group column in target list
template1=> SELECT nome,id FROM gruppi GROUP BY nome;
ERROR: Illegal use of aggregates or non-group column in target list
And now the output query with SELECT DISTINCT:
template1=> SELECT DISTINCT ON g.nome g.nome,u.uid,u.id from gruppi g,
usergroup u;
ERROR: parser: parse error at or near "."
--
Best Regards
----------------------------
Giampiero Raschetti
Sistemi Innovativi
Banca Popolare di Sondrio
----------------------------
At 14:08 +0300 on 25/08/1999, Giampiero Raschetti wrote: > And now the output query with GROUP BY: > > template1=> SELECT g.nome,u.uid,u.id FROM gruppi g, usergroup u GROUP BY > g.nome; > ERROR: Illegal use of aggregates or non-group column in target list > template1=> SELECT nome,id FROM gruppi GROUP BY nome; > ERROR: Illegal use of aggregates or non-group column in target list > > And now the output query with SELECT DISTINCT: > > template1=> SELECT DISTINCT ON g.nome g.nome,u.uid,u.id from gruppi g, > usergroup u; > ERROR: parser: parse error at or near "." I think you have a misunderstanding about the purpose of group by statements. In fact, the above seems to indicate that you are not well aware of what joins are, or at least you don't know that you have to restrict them to make sense of your data. You really have to add WHERE g.id = u.id. Anyway, what was it that you wanted to display in that second query of yours? For each nome, display some id that matches it? Define "some". Which id do you want. DON'T use "distinct on". It makes no sense. SQL is defined in such a way that the returned set of results will be the same in all implementations that respect the standard. If you use "DISTINCT ON", it will pick up a representative at will, and it may not be the same representative picked by other implementations. So... it is not a standard part of SQL. In order to be more standard, you have to tell it how to pick the representative. For example, tell him to give you the smallest-number id that matches a group. This is done with min(): SELECT g.nome, min(u.uid), min(u.id) FROM gruppi g, usergroup u WHERE g.id = u.id GROUP BY g.nome; This will work. The question is whether this is what you wanted. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Giampiero Raschetti <Giampiero.Raschetti@popso.it> writes:
> And now the output query with GROUP BY:
> template1=> SELECT g.nome,u.uid,u.id FROM gruppi g, usergroup u GROUP BY
> g.nome;
> ERROR: Illegal use of aggregates or non-group column in target list
> template1=> SELECT nome,id FROM gruppi GROUP BY nome;
> ERROR: Illegal use of aggregates or non-group column in target list
That is not a bug, it is the way things are supposed to work --- you
have variables in your SELECT that are not being grouped over, and are
not inside an aggregate function, so they have no well-defined value.
Postgres before 6.5 was not very good about catching this error all
the time, but 6.5 includes a more complete error check.
> And now the output query with SELECT DISTINCT:
> template1=> SELECT DISTINCT ON g.nome g.nome,u.uid,u.id from gruppi g,
> usergroup u;
> ERROR: parser: parse error at or near "."
It looks like DISTINCT ON wants a plain unqualified column name.
DISTINCT ON is not standard SQL, and IMHO we ought to take it out ---
it's not well-defined. It's got the same definitional problem as
non-group-by columns: what values of the other variables are you going
to get?
regards, tom lane
Herouth Maoz wrote: > > At 14:08 +0300 on 25/08/1999, Giampiero Raschetti wrote: > > > ............................... > > I think you have a misunderstanding about the purpose of group by statements. > > In fact, the above seems to indicate that you are not well aware of what > joins are, or at least you don't know that you have to restrict them to > make sense of your data. You really have to add WHERE g.id = u.id. > OK. That's true. I'm not well aware of SQL in general but if you try out that secuence into postgres 6.4.2 you will see that it works. So I understand that probably it was not standard. Well. Let's take a look at this same problem without any join involved and if possible let me know how to obtain my target: xaxa=> \d comuni Table = comuni +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | produtt | varchar() | 40 | | unital | varchar() | 40 | | comune | varchar() | 40 | | login | varchar() | 8 | +----------------------------------+----------------------------------+-------+ xaxa=> select * from comuni; produtt| unital|comune |login -------+---------------+------------+----- xxx |199900200000100|POGGIRIDENTI|user1 yyy |199900200000100|POGGIRIDENTI|user2 yyy |199900200000100|ARDENNO |USER3 yyy |199900200000100|BORMIO |user2 (4 rows) xaxa=> select * from comuni group by login; ERROR: Illegal use of aggregates or non-group column in target list xaxa=> This works too on Postgres 6.4.2 so how can I obtain a list of unique users from this table on 6.5.1 ? Many Thanks -- Best Regards ---------------------------- Giampiero Raschetti Sistemi Innovativi Banca Popolare di Sondrio ----------------------------
On Wed, Aug 25, 1999 at 03:58:29PM +0300, Herouth Maoz wrote:
> At 14:08 +0300 on 25/08/1999, Giampiero Raschetti wrote:
...
> In fact, the above seems to indicate that you are not well aware of what
> joins are, or at least you don't know that you have to restrict them to
> make sense of your data. You really have to add WHERE g.id = u.id.
Of course this is not at the core of Giampiero's problem, but there
are instances where you don't use a restriction, i.e. exactly when you
want to get the Cartesian product of the tables. Suppose, e.g., that
you want to generate a list of all the fields on a chess board. You
can do this by
create table rows (l char);
create table cols (n int2);
insert into rows ('a');
... (all the way to 'h')
insert into cols (1);
... (all the way to 8);
select l, n from rows, cols;
Albert.
--
--------------------------------------------------------------------------- Post an / Mail to / Skribu al: Albert
Reiner<areiner@tph.tuwien.ac.at>
---------------------------------------------------------------------------