Insert statement fails silently when a FK constaint can't be statically evaluated I ran into an interesting issue which I'm not entirely sure how to address. 

My tables:

CREATE TABLE entity (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  properties jsonb
);

CREATE TABLE hierarchy (
  parent_id int REFERENCES entity(id) NOT NULL,
  child_id int REFERENCES entity(id) NOT NULL,
  PRIMARY KEY(parent_id, child_id)
);


And some dummy data:

INSERT INTO entity (id, properties)
VALUES (1234, '{}'::jsonb);


The issue occurs when inserting data into hierarchy which violates this FK constraint. The following doesn't work, as expected, and returns a helpful error message:

WITH entity_result AS ( SELECT 5678 AS id )
INSERT INTO hierarchy(parent_id, child_id)
SELECT 1234, id FROM entity_result;

-- [23503] ERROR: insert or update on table "hierarchy" violates foreign key constraint "hierarchy_child_id_fkey" Detail: Key (child_id)=(5678) is not present in table "entity".


However, when doing another simiiar query where the ID's value cannot be statically assessed, instead of reporting the error it just fails silently. For example, the following query:

WITH entity_result AS (
  SELECT id FROM entity
  WHERE properties-&gt;&gt;'member_id' = '92385' )
INSERT INTO hierarchy(parent_id, child_id)
SELECT 1234, id FROM entity_result;

-- ...silence


Simply fails, and without any indication as to why. 

Is there any way to get some information about why a query like the above fails? It's baffling me!
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