Postgres Query To Get All The Children Ids

I'm an SQL noob and wrote only very basic queries so far. I have a table that looks like this item_full_name varchar(65535) item_id bigint item_owners var

Solution 1:

I started off going down the road of a recursive CTE, but then realized that you just want the children of each parent, at that single level. One approach is to aggregate the item_id by item_parent_id. Then, join your original table to this result to obtain the CSV list of children for each parent.

WITH cte AS (
    SELECT item_parent_id, STRING_AGG(item_id::text, ',') AS item_childrenFROM yourTable
    GROUPBY item_parent_id
)

SELECT
    t1.item_full_name,
    t1.item_id,
    t1.item_owners,
    t1.item_parent_id,
    t2.item_children
FROM yourTable t1
LEFT JOIN cte t2
    ON t1.item_id = t2.item_parent_id
ORDERBY
    t1.item_full_name;

Demo