pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: William D. McCoy <wdmccoy@geo.umass.edu>
To: pgsql-sql@postgreSQL.org
Subject: problem with 'insert into...'
Date: Tue, 23 Jun 1998 15:25:32 -0400 (EDT)
Message-ID: <199806231925.PAA15195@aeolus.geo.umass.edu> (raw)

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



Message-ID: <199806231925.PAA15195@aeolus.geo.umass.edu>
Permalink:  ../199806231925.PAA15195@aeolus.geo.umass.edu/
Also on:    postgresql.org/message-id/199806231925.PAA15195@aeolus.geo.umass.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: wdmccoy@geo.umass.edu
  Subject: Re: problem with 'insert into...'
  In-Reply-To: <199806231925.PAA15195@aeolus.geo.umass.edu>
  References: <199806231925.PAA15195@aeolus.geo.umass.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox