Kevin Johnson, PostgreSQL

Language: Layout:

CREATE SEQUENCE path_id_seq;
CREATE TABLE path (
    id INTEGER PRIMARY KEY DEFAULT NEXTVAL('path_id_seq'),
    parent_id INTEGER,
    name TEXT NOT NULL);

CREATE UNIQUE INDEX path_parent_id_name_ix
    ON path (COALESCE(parent_id, 0), name);
    
insert into path (id, name, parent_id)
select id, name, parent_id
from (
  select *,
         case 
           when is_new
             then lag(id) over (order by level) 
             else existing_parent
         end as parent_id
  from (
    SELECT case 
              when p.id is null then nextval('path_id_seq') 
              else p.id
           end as id, 
           x.name, x.level, p.parent_id as existing_parent, 
           p.id is null as is_new        
    FROM unnest(string_to_array('Assets/images/i1.png', '/')) with ordinality as x(name, level)
      LEFT JOIN path p on p.name = x.name
    order by level
  ) t1
) t2
where is_new
;

insert into path (id, name, parent_id)
select id, name, parent_id
from (
  select *,
         case 
           when is_new
             then lag(id) over (order by level) 
             else existing_parent
         end as parent_id
  from (
    SELECT case 
              when p.id is null then nextval('path_id_seq') 
              else p.id
           end as id, 
           x.name, x.level, p.parent_id as existing_parent, 
           p.id is null as is_new        
    FROM unnest(string_to_array('Assets/images/i2.png', '/')) with ordinality as x(name, level)
      LEFT JOIN path p on p.name = x.name
    order by level
  ) t1
) t2
where is_new
;

insert into path (id, name, parent_id)
select id, name, parent_id
from (
  select *,
         case 
           when is_new
             then lag(id) over (order by level) 
             else existing_parent
         end as parent_id
  from (
    SELECT case 
              when p.id is null then nextval('path_id_seq') 
              else p.id
           end as id, 
           x.name, x.level, p.parent_id as existing_parent, 
           p.id is null as is_new        
    FROM unnest(string_to_array('Assets/pictures/p1.png', '/')) with ordinality as x(name, level)
      LEFT JOIN path p on p.name = x.name
    order by level
  ) t1
) t2
where is_new
;

insert into path (id, name, parent_id)
select id, name, parent_id
from (
  select *,
         case 
           when is_new
             then lag(id) over (order by level) 
             else existing_parent
         end as parent_id
  from (
    SELECT case 
              when p.id is null then nextval('path_id_seq') 
              else p.id
           end as id, 
           x.name, x.level, p.parent_id as existing_parent, 
           p.id is null as is_new        
    FROM unnest(string_to_array('Assets/pictures/p2.png', '/')) with ordinality as x(name, level)
      LEFT JOIN path p on p.name = x.name
    order by level
  ) t1
) t2
where is_new
;

select *
from path;


absolute service time: 0,75 sec