Insert statement fails silently when a FK constaint can't be statically evaluated
My bet is that:

SELECT id FROM entity
WHERE properties-&gt;&gt;'member_id' = '92385'


returns NO ROWS (NO ERROR) and INSERT command is never executed.

I've reproduced it in this way:

create table entity 
(
  id int primary key, 
  foo int
);

create table hierarchy
(
  id serial primary key, 
  parent_id int,
  child_id int,
  foreign key (parent_id) references entity (id),
  foreign key (child_id) references entity (id)
);

insert into entity
select x, x * 2
from   generate_series(1, 100) x;






WITH entity_result AS (
  SELECT id FROM entity
  WHERE id = 10000 )
INSERT INTO hierarchy(parent_id, child_id)
SELECT 100, id FROM entity_result;



✓






SELECT * FROM hierarchy;



id | parent_id | child_id
-: | --------: | -------:






INSERT INTO hierarchy VALUES (100, NULL);



1 rows affected






SELECT * FROM hierarchy;



 id | parent_id | child_id
--: | --------: | -------:
100 |      null |     null



db&lt;>fiddle here