Problem with group by command - Mailing list pgsql-bugs

Dear PGSQL-BUG group,

I would be grateful if any of you could provide me any information that you
may have.

I have written a perl program which runs fine on a machine that was using
PostgreSQL 6.4.2.  I setup another machine which was running PostgreSQL
6.5.3 bundled by the Linux 6.2.  I used the copy command to migrate most of
the tables on the new machine and re-run the same program.

The same error message keeps on appear said that:
Invalid use of aggregates or non-group column in target list.

The SQL statement used is:
select username, user_index, date, status from subscribers, cellnet_mesg_log
where status != 3 and date >= '25/10/2000' and date < '01/11/2000' and
cellnet_mesg_log.user_index = subscribers.oid group by username;

The 'group by' works in the new machine only if the selected data contains
username which is so weird.  I wonder whether some of the attributes might
have been changed since I did not restore the same tables from pg_dumpall
command.
If that is the case, I wonder if I could manually compare and reset the
attributes.
Unfortunately, I couldn't find this information from your web page.

The tables structure are as below.  The cellnet_mesg_log's user_index is map
with the oid of the subscribers table.

Table    = subscribers
+----------------------------------+----------------------------+
|Field                             |Type                | Length|
+----------------------------------+--------------------+-------+
| username                         | varchar()          |    32 |
| acct_no                          | int4               |     4 |
| acct_type                        | int4               |     4 |
| build_factor                     | int4               |     4 |
+----------------------------------+-----------------------------


Table    = cellnet_mesg_log
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| user_index                       | oid                              |
4 |
| date                             | datetime                         |
8 |
| sender                           | varchar()                        |
64 |
| part                             | int4                             |
4 |
| part_total                       | int4                             |
4 |
| status                           | int4                             |
4 |
| mesg_id                          | varchar()                        |
64 |
| message                          | varchar()                        |
160 |
+----------------------------------+----------------------------------+-----
--+

Hope to hear from any of you soon.  Thank you for your kind attention.


regards,
Jessica Ord