Postgresql递归查询未提供预期结果

我有一个PostgreSQL表,它将记录存储为树值

create table doitestchk(node_id,entity_id,letter,parent_id)as values
 (1,'Entity_id_1','10',null)
,(2,'Entity_id_1','93/rheumatology',1)
,(3,'Entity_id_1','1093',1)
,(4,'Entity_id_1','rheumatology',3)
,(5,'Entity_id_1','journals.rheumatology.a0314',3)
,(6,'Entity_id_1','2015002908',3)
,(7,'Entity_id_1','icc',3)
,(8,'Entity_id_1','8',5)
,(9,'Entity_id_1','9',5);

当孩子询问时,我想得到所有的家长信 例子:

WITH RECURSIVE generation AS (
    SELECT node_id,
           entity_id,
           letter,
           parent_id,
           0 AS generation_number
    FROM doitestchk
    WHERE parent_id IS NULL
    UNION ALL
    SELECT child.node_id,
           child.entity_id,
           child.letter,
           child.parent_id,
           generation_number + 1 AS generation_number
    FROM doitestchk child
    JOIN generation g
      ON g.node_id = child.parent_id
) 
SELECT entity_id,
       letter,
       generation_number
FROM generation 
WHERE letter like '%a0314%';

我只得到node_id:5 相反,我需要node_id:5的所有父节点,即node_id 3,即node_id:1的子节点

有什么方法可以获取所有子节点的父节点吗?