How to make an array from a SELECT returning more than one row

Question

Is it possible to make one big array from a query like:

select
array_append(ARRAY[0], console_id)
from archive_sessions
where tournament_id = 14817

I tried with group by but I have to use console_id in it and it still is more than 1 row.

And how in this query initializing an empty ARRAY[]?

Answer 1

If the query only returns column(s) that go into the array, use an ARRAY constructor:

SELECT ARRAY(SELECT console_id FROM archive_sessions
             WHERE  tournament_id = 14817) AS console_arr;

This is typically faster than array_agg() for the simple case.

Answer 2

You want array_agg

select array_agg(console_id) as consoles from archive_sessions where tournament_id = 14817