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;
$$;

Why are you doing this? What is the real problem you are trying to solve? — user330315
– user330315, Commented Nov 27, 2015 at 9:45
I would like to sort the ranges in the array by descending right boundaries. I am thinking of making a table and sorting it with upper() or something — alisianoi
– alisianoi, Commented Nov 27, 2015 at 9:46
@a_horse_with_no_name here is my comment recast into a question: stackoverflow.com/questions/33954733/… — alisianoi
– alisianoi, Commented Nov 27, 2015 at 9:56

Tomasz Jakub Rup · Accepted Answer · 2015-11-27 09:42:33Z

4

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.

answered Nov 27, 2015 at 9:42

Tomasz Jakub Rup

10.7k7 gold badges52 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related