PostgreSQL の array_agg() で select 結果を配列に格納

やりたいこと

select した結果を配列で取得する。
例えば select の結果が 1、2、3 の場合、配列の [1, 2, 3] として検索結果を取得する。

select の結果を配列で取得

テーブル定義例

create table test1 (
  id    integer generated always as identity primary key,
  name  varchar(32) not null
);

insert into test1 (name) values ('aaa');
insert into test1 (name) values ('bbb');
insert into test1 (name) values ('ccc');

array_agg()

array_agg({フィールド}) で select の結果を配列として取得することができる。

# select
  array_agg(name order by name)
from
  test1;

   array_agg
---------------
 {aaa,bbb,ccc}
(1 row)

select 結果が空の場合には null が返却される。

# select
  array_agg(name order by name)
from
  test1
where
  id = 4;

 array_agg
-----------

(1 row)

select 結果が空の場合に空の配列を返すようにするには、
coalesce() で null の場合に空の配列を指定する。

select
  coalesce(array_agg(name order by name), array[]::varchar[])
from
  test1
where
  id = 4;

 coalesce
----------
 {}
(1 row)

配列の n 番目の値を参照するには、[n] で要素を指定する。

# select
  (array_agg(name order by name))[1]
from
  test1;

 array_agg
-----------
 aaa
(1 row)