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
