title: Postgres query to get all the children ids
body: <p>I'm an SQL noob and wrote only very basic queries so far.</p>
  
  <p>I have a table that looks like this </p>
  
  <pre><code>item_full_name      varchar(65535)
  item_id             bigint
  item_owners         varchar(255)
  item_approver_group varchar(255)
  item_state          varchar(255)
  item_parent_id      bigint
  item_children       varchar(65535)
  </code></pre>
  
  <p>Initially item_children is empty for all the rows but each item has a item_parent_id and is not null. I want to write a query that looks at all the rows &amp; corresponding parent ids and updates each row's item_children with a string of children ids separated by comma.</p>
  
  <p>for eg.</p>
  
  <pre><code>item_full_name | item_id | item_owners | item_parent_id | item_children
  item1          | 1       | o1, o2      | 2              | 
  item2          | 3       | owner8      | 2              |
  item3          | 2       | owner6      | 0              |
  item4          | 4       | owner7      | 1              |
  </code></pre>
  
  <p>This should be transformed to</p>
  
  <pre><code>item_full_name | item_id | item_owners | item_parent_id | item_children
  item1          | 1       | o1, o2      | 2              | 4
  item2          | 3       | owner8      | 2              |
  item3          | 2       | owner6      | 0              | 3,1
  item4          | 4       | owner7      | 1              |
  </code></pre>
  
  <p>Any pointers would be helpful. Thanks!</p>