PostgreSQL recursive querying to build a hierarchy

pg_recursive_query.rb

require 'pg'

PG.connect(dbname: 'postgres').tap do |db|
  db.exec "drop database if exists postgresql_experiments"
  db.exec "create database postgresql_experiments"
end

PG.connect(dbname: 'postgresql_experiments').exec(<<~SQL).to_a
create table tree (id int, parent_id int);

insert into tree (id, parent_id) values
           (4,null),
     (2,4),        (6,4),
  (1,2),(3,2),  (5,6),(7,6)
;


with recursive ancestries (id, ancestry) as (
  select id, array_agg(id)
  from tree
  where parent_id is null
  group by id

  union

  select tree.id, array_append(ancestries.ancestry, tree.id)
  from ancestries
  inner join tree on (tree.parent_id = ancestries.id)
)
select * from ancestries;
SQL
# => [{"id"=>"4", "ancestry"=>"{4}"},
#     {"id"=>"2", "ancestry"=>"{4,2}"},
#     {"id"=>"6", "ancestry"=>"{4,6}"},
#     {"id"=>"1", "ancestry"=>"{4,2,1}"},
#     {"id"=>"3", "ancestry"=>"{4,2,3}"},
#     {"id"=>"5", "ancestry"=>"{4,6,5}"},
#     {"id"=>"7", "ancestry"=>"{4,6,7}"}]