Prevent postgres from inlining an update subquery

Question

I have a table

CREATE TABLE author (
    id SERIAL PRIMARY KEY,
    followers INTEGER[]
);
INSERT INTO author (followers)
       SELECT '{}'::INTEGER[]
       FROM generate_series(0, 1000);

I populated it with ids and want to add random followers. Problem arises when I run

UPDATE author
SET followers = (SELECT array_agg(id)
                 FROM author
                 WHERE random() < 0.01);

Postgres is tries to be smart and executes the SELECT only once, causing same value to be repeated over and over again. What is the correct way of achieving this? I tried using OFFSET 0 and UDPATE .. FROM .. but to no avail

Answer 1

I believe that you need to create an artificial dependency between the subquery and the outer query. Something as simple as:

UPDATE Author AS A1
SET
    followers = (
             SELECT array_agg(id)
             FROM Author
             WHERE
                 A1.author_id = A1.author_id AND
                 random() < 0.01);

(I guessed at a column name of author_id, but any column should work)