PostgreSQL将非递归项的输出转换为正确的类型

Question

我有一个下面的PostgreSQL查询：

WITH RECURSIVE disease_tree AS
(
SELECT ref_disease_id, uid, parent, level, NULL AS subtype, NULL AS specific_subtype
FROM ref_disease
WHERE parent IS NULL

UNION ALL

SELECT d.ref_disease_id, d.uid, d.parent, d.level,  
  CASE 
      WHEN d.level = 2 THEN d.uid
      ELSE dtr.subtype END AS subtype,
  CASE 
      WHEN d.level = 3 THEN d.name
      ELSE NULL END AS specific_subtype
FROM ref_disease As d
INNER JOIN disease_tree AS dtr
ON d.parent = dtr.uid
)
select ref_disease_id, uid, level, subtype, specific_subtype from disease_tree 

到目前为止，一切运行正常。但是根据我的应用程序逻辑，我必须在以下行返回d.uid而不是d.name：WHEN d.level = 3 THEN d.name

因此，新的查询如下所示：

WITH RECURSIVE disease_tree AS
(
SELECT ref_disease_id, uid, parent, level, NULL AS subtype, NULL AS specific_subtype
FROM ref_disease
WHERE parent IS NULL

UNION ALL

SELECT d.ref_disease_id, d.uid, d.parent, d.level,  
  CASE 
      WHEN d.level = 2 THEN d.uid
      ELSE dtr.subtype END AS subtype,
  CASE 
      WHEN d.level = 3 THEN d.uid
      ELSE NULL END AS specific_subtype
FROM ref_disease As d
INNER JOIN disease_tree AS dtr
ON d.parent = dtr.uid
)
select ref_disease_id, uid, level, subtype, specific_subtype from disease_tree 

但它失败了，并显示以下错误：

ERROR:  recursive query "disease_tree" column 6 has type text in non-recursive term but type character varying overall
LINE 3: ..._disease_id, uid, parent, level, NULL AS subtype, NULL AS sp...
                                                             ^
HINT:  Cast the output of the non-recursive term to the correct type.
********** Error **********

ERROR: recursive query "disease_tree" column 6 has type text in non-recursive term but type character varying overall
SQL state: 42804
Hint: Cast the output of the non-recursive term to the correct type.
Character: 107

如何转换输出并修复它？

Answer 1

case表达式返回单个类型，该类型派生自then和where子句中的类型。默认情况下，NULL是文本(我认为)。

您的案例看起来像是混合类型。因此，转换这些值。Postgres具有::<type>的简写形式。

(CASE WHEN d.level = 2 THEN d.uid::text
      ELSE dtr.subtype::text
 END) AS subtype,

您希望在CASE中进行转换，否则在尝试将字符串转换为数字时可能会出现运行时错误。