mgoszcz2 mgoszcz2 - 6 months ago 9
SQL Question

Prevent postgres from inlining an update subquery

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

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)

Comments