aggregate functions are not allowed in UPDATE

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Good evening,

I have prepared a simple test case for my question: https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/0

There I create 3 tables:

CREATE TABLE users (
  uid SERIAL PRIMARY KEY,
  avg_time TEXT
);
 
CREATE TABLE games (
  gid SERIAL PRIMARY KEY,
  player1 INTEGER NOT NULL REFERENCES users(uid) ON DELETE CASCADE,
  player2 INTEGER NOT NULL REFERENCES users(uid) ON DELETE CASCADE CHECK (player1 <> player2)
);

CREATE TABLE moves (
  mid BIGSERIAL PRIMARY KEY,
  played  timestamptz NOT NULL,
  gid INTEGER NOT NULL REFERENCES games(gid) ON DELETE CASCADE,
  uid INTEGER NOT NULL REFERENCES users(uid) ON DELETE CASCADE
);

And then I fill them with 3 test players and 2 test games (1 vs. 3 and 1 vs. 2):

INSERT INTO users (uid) VALUES
  (1),
  (2),
  (3);

INSERT INTO games (gid, player1, player2) VALUES
  (1, 2, 3),
  (2, 1, 3),
  (3, 1, 2);
 
INSERT INTO moves (played, gid, uid) VALUES
  (NOW() + INTERVAL '1 hour', 2, 1),
  (NOW() + INTERVAL '2 hour', 2, 3),
  (NOW() + INTERVAL '3 hour', 2, 1), -- +1 hour
  (NOW() + INTERVAL '4 hour', 2, 3),
  (NOW() + INTERVAL '5 hour', 2, 1), -- +1 hour
  (NOW() + INTERVAL '6 hour', 2, 3),
  (NOW() + INTERVAL '7 hour', 2, 1), -- +1 hour
  (NOW() + INTERVAL '8 hour', 2, 3),
 
  (NOW() + INTERVAL '10 hour', 3, 1),
  (NOW() + INTERVAL '20 hour', 3, 2),
  (NOW() + INTERVAL '30 hour', 3, 1), -- +10 hours
  (NOW() + INTERVAL '40 hour', 3, 2),
  (NOW() + INTERVAL '50 hour', 3, 1), -- +10 hours
  (NOW() + INTERVAL '60 hour', 3, 2),
  (NOW() + INTERVAL '70 hour', 3, 1), -- +10 hours
  (NOW() + INTERVAL '80 hour', 3, 2);

After that I am able to calculate the average time that the player 1 needs to perform a move (11 hours):

WITH diffs AS (
  SELECT
      gid,
      uid,
      played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
  FROM moves
  WHERE uid = 1
)
SELECT
  uid, AVG(diff)
FROM diffs
GROUP BY uid;

But how to take that calculated value and store it in the "avg_time" text column of the users table?

When I am trying

WITH diffs AS (
  SELECT
      gid,
      uid,
      played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
  FROM moves
  WHERE uid = 1
)
UPDATE users SET
avg_time = TO_CHAR(AVG(diff), 'HH24:MI')
FROM diffs;

the syntax error is unfortunately printed by PostgreSQL 10:

    aggregate functions are not allowed in UPDATE

Regards
Alex