Received: from aeolus.geo.umass.edu (aeolus.geo.umass.edu [128.119.45.25]) by hub.org (8.8.8/8.7.5) with ESMTP id PAA22800 for ; Tue, 23 Jun 1998 15:27:43 -0400 (EDT) Received: (from wdmccoy@localhost) by aeolus.geo.umass.edu (8.8.4/8.6.9) id PAA15195; Tue, 23 Jun 1998 15:25:32 -0400 (EDT) Date: Tue, 23 Jun 1998 15:25:32 -0400 (EDT) Message-Id: <199806231925.PAA15195@aeolus.geo.umass.edu> From: "William D. McCoy" MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit To: pgsql-sql@postgreSQL.org Subject: problem with 'insert into...' X-Mailer: VM 6.34 under 20.3 "Vatican City" XEmacs Lucid I am trying to work around the lack of an outer join function in postgreSql. I am trying to construct a full outer join of two identically defined tables, each of which contains (along with other data), a value for a particular lab sample. I have come across the following problem. When I execute the following query it returns the expected result (816 rows returned). select f.lab_no, f.prep_no, avg(f.fai), NULL, f.peak_values from free_ratios f where not exists ( select * from hyd_ratios h where h.lab_no = f.lab_no and h.prep_no = f.prep_no and h.run_no = f.run_no ) group by f.peak_values, f.lab_no, f.prep_no; However, when I add an 'insert into table_name' to the query like this: insert into both_ratios_avg select f.lab_no, f.prep_no, avg(f.fai), NULL, f.peak_values from free_ratios f where not exists ( select * from hyd_ratios h where h.lab_no = f.lab_no and h.prep_no = f.prep_no and h.run_no = f.run_no ) group by f.peak_values, f.lab_no, f.prep_no; I get the following error message: ERROR: parser: aggregates not allowed in GROUP BY clause There are clearly no aggregates in my GROUP BY clause. I get the same error message with the following query, although, just like the example above, the select by itself works fine: insert into both_ratios_avg select f.lab_no, f.prep_no, avg(fai), avg(hai), f.peak_values from free_ratios f, hyd_ratios h where f.lab_no = h.lab_no and f.prep_no = h.prep_no and f.run_no = h.run_no group by f.peak_values, f.lab_no, f.prep_no; Just to make things more interesting, the following very similar query works fine and does not give any error: insert into both_ratios_avg select h.lab_no, h.prep_no, NULL, avg(hai), h.peak_values from hyd_ratios h where not exists ( select * from free_ratios f where f.lab_no = h.lab_no and f.prep_no = h.prep_no and f.run_no = h.run_no ) group by h.peak_values, h.lab_no, h.prep_no; (This is another third of my full outer join workaround.) Any ideas about what might be happening here? -- William D. McCoy Geosciences University of Massachusetts Amherst, MA 01003