Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


-- setup
create table organization ( id         integer primary key
                          , name       text
                          , parent_id  integer references organization(id) 
                          , constraint parent_not_self check (parent_id <> id) 
                          ) ;
                         
create unique index organization_not__mirrored  
       on organization( least(id,parent_id), greatest(id,parent_id) );

              
           
insert into organization( id,  parent_id, name) 
     values (3,null,'Test3') 
          , (1,3,'Test1')          
          , (2,1,'Test2')
          , (4,2,'Test4');
          
select * 
  from organization;

CREATE TABLE



CREATE INDEX



INSERT 0 4




id
name
parent_id


3
Test3
null

1
Test1
3

2
Test2
1

4
Test4
2



SELECT 4


-- show hierarchy
with recursive hier(parent_id, name, child_id, path, level) as 
     ( select id,name, parent_id, id::text, 1 
         from organization 
        where parent_id is null 
       union all 
       select o.id, o.name, o.parent_id,h.path || '->' ||o.id::text,h.level+1 
         from organization o 
         join hier        h
           on (o.parent_id = h.parent_id)
     ) 
select * from hier; 


parent_id
name
child_id
path
level


3
Test3
null
3
1

1
Test1
3
3->1
2

2
Test2
1
3->1->2
3

4
Test4
2
3->1->2->4
4



SELECT 4


-- extend depth of hierarchy 
insert into organization(id, parent_id, name)  
       select i, i-1, 'Test' || i::text
         from generate_series( 5, 10) gs(i);
         
with recursive hier(parent_id, name, child_id, path, level) as 
     ( select id,name, parent_id, id::text, 1 
         from organization 
        where parent_id is null 
       union all 
       select o.id, o.name, o.parent_id,h.path || '->' ||o.id::text,h.level+1 
         from organization o 
         join hier         h
           on (o.parent_id = h.parent_id)
     ) 
select * from hier;

INSERT 0 6




parent_id
name
child_id
path
level


3
Test3
null
3
1

1
Test1
3
3->1
2

2
Test2
1
3->1->2
3

4
Test4
2
3->1->2->4
4

5
Test5
4
3->1->2->4->5
5

6
Test6
5
3->1->2->4->5->6
6

7
Test7
6
3->1->2->4->5->6->7
7

8
Test8
7
3->1->2->4->5->6->7->8
8

9
Test9
8
3->1->2->4->5->6->7->8->9
9

10
Test10
9
3->1->2->4->5->6->7->8->9->10
10



SELECT 10


-- insert into middle of hierarchy
        
insert into organization(id, parent_id, name)
   values (35,7,'Test35') ;

update organization   
   set parent_id = 35
  where id=8;
  with recursive hier(parent_id, name, child_id, path, level) as 
     ( select id,name, parent_id, id::text, 1 
         from organization 
        where parent_id is null 
       union all 
       select o.id, o.name, o.parent_id,h.path || '->' ||o.id::text,h.level+1 
         from organization o 
         join hier         h
           on (o.parent_id = h.parent_id)
     ) 
select * from hier;

INSERT 0 1



UPDATE 1




parent_id
name
child_id
path
level


3
Test3
null
3
1

1
Test1
3
3->1
2

2
Test2
1
3->1->2
3

4
Test4
2
3->1->2->4
4

5
Test5
4
3->1->2->4->5
5

6
Test6
5
3->1->2->4->5->6
6

7
Test7
6
3->1->2->4->5->6->7
7

35
Test35
7
3->1->2->4->5->6->7->35
8

8
Test8
35
3->1->2->4->5->6->7->35->8
9

9
Test9
8
3->1->2->4->5->6->7->35->8->9
10

10
Test10
9
3->1->2->4->5->6->7->35->8->9->10
11



SELECT 11


-- Restriction 1: create (parent, child) --> (x,x) 
insert into organization(id, parent_id, name)   
      values (40, 40, 'Test40');

ERROR:  new row for relation "organization" violates check constraint "parent_not_self"
DETAIL:  Failing row contains (40, Test40, 40).


insert into organization(id, parent_id, name)   
      values (50, null, 'Test50'), (51, null, 'Test51'); 
     
update organization  
   set parent_id = 50 
 where id = 51;

update organization  
   set parent_id = 51 
 where id = 50;
 

INSERT 0 2



UPDATE 1



ERROR:  duplicate key value violates unique constraint "organization_not__mirrored"
DETAIL:  Key (LEAST(id, parent_id), GREATEST(id, parent_id))=(50, 51) already exists.
parent_id	name	child_id	path	level
3	Test3	null	3	1
1	Test1	3	3->1	2
2	Test2	1	3->1->2	3
4	Test4	2	3->1->2->4	4
5	Test5	4	3->1->2->4->5	5
6	Test6	5	3->1->2->4->5->6	6
7	Test7	6	3->1->2->4->5->6->7	7
8	Test8	7	3->1->2->4->5->6->7->8	8
9	Test9	8	3->1->2->4->5->6->7->8->9	9
10	Test10	9	3->1->2->4->5->6->7->8->9->10	10