postgresql request database sql

Question

my db contains 2 tables category and item:

----------       ------------------
 category                item
----------       ------------------
id                 id  category_id
1                  1      1 
2                  2      1 
3                  3      2
4                  4      2
                   5      2
                   6      3
                   7      4
                   8      4

which request sql will give this result:

category.id 1 [
      item.id 1
      item.id 2 
      ],
category.id 2 [
      item.id 3
      item.id 4
      item.id 5
      ],
category.id 3 [
      item.id 6
      ],
category.id 4 [
      item.id 7,
      item.id 8
      ];

question from:https://stackoverflow.com/questions/65848414/postgresql-request-database-sql

Answer 1

Probably you want something like this:

select id, array_agg(category_id) 
from item
group by id;