PostgreSQL: `unnest` an array and then `array_agg` it back

Question

My goal is to take an array, unnest it into a table with unnest and then aggregate it back into an array with array_agg. Why does the first DO block fail and the second succeed?

DO $$
DECLARE
    x numrange[] := '{"[0, 3]", "[0, 1]", "[3, 5]", "[3, 8]"}';
BEGIN

    x := (SELECT array_agg(x) FROM unnest(x));
    RAISE NOTICE '%', x;
END;
$$;


DO $$
DECLARE
    x numrange[] := '{"[0, 3]", "[0, 1]", "[3, 5]", "[3, 8]"}';
BEGIN

    x := (SELECT array_agg(y) FROM unnest(x) AS y);
    RAISE NOTICE '%', x;
END;
$$;

Answer 1

In first DO name of the column is unnest. You don't have column x.

DO $$
DECLARE
    x numrange[] := '{"[0, 3]", "[0, 1]", "[3, 5]", "[3, 8]"}';
BEGIN

    x := (SELECT array_agg(unnest) FROM unnest(x));
    RAISE NOTICE '%', x;
END;
$$;

---

In second DO name of the column is y and You can do string_agg() on this column.