PostgreSQL: UPDATE using an aggregate function

Question

I want to update a table paneldataby defining a column ibaseusing an aggregate function.

UPDATE paneldata p
SET ibase=SUM(1/i.dist)
FROM ibaselang i
WHERE p.gid=i.gid
AND i.instp<p.period

The result is ERROR: aggregate functions are not allowed in UPDATE

TABLE DEFINITIONS

CREATE TABLE public.ibaselang
(
  gid integer,
  dist double precision,
  buildid integer,
  instp smallint
)
WITH (
  OIDS=FALSE
);

Solution approach

Unfortunately, I do not know how to implement my functions WHEREin a subquery.

Answer 1

Try using a correlated query as follows:

UPDATE paneldata p
SET p.ibase=(select SUM(1/i.dist)
             FROM ibaselang i
             WHERE p.gid=i.gid
             AND i.instp<p.period)

Answer 2

I don't know how effective this is, but it should work:

with updates as (
  select
    p.gid, sum (i.dist) as dist
  from
    paneldata p
    join ibaselang i on
      p.id = i.gid and
      i.instp < p.period
  where
    i.dist != 0
  group by
    p.gid
)
update paneldata p
set
  ibase = 1 / u.dist
from
  updates u
where
  p.gid = u.pid and
  u.dist != 0

A few other add-on notes:

• i.dist , where
• , . , , .