Postgres CTE:非递归术语中的字符类型为variant(255)[],但总体而言字符类型为variable [] [英] Postgres CTE : type character varying(255)[] in non-recursive term but type character varying[] overall
问题描述
我是SO和postgres的新手,所以请原谅我的无知。尝试使用类似于本文中的解决方案的方法在postgres中获得图的群集。在PostgreSQL中查找给定节点的群集
I am new to SO and postgres so please excuse my ignorance. Attempting to get the cluster for a graph in postgres using a solution similar to the one in this post Find cluster given node in PostgreSQL
唯一的区别是我的ID是UUID,我正在使用varchar(255)存储此ID
the only difference is my id is a UUID and I am using varchar(255) to store this id
当我尝试运行查询时,出现以下错误(但不确定如何投射):
when i try to run the query I get the following error (but not sure how to cast):
ERROR: recursive query "search_graph" column 1 has type character varying(255)[] in non-recursive term but type character varying[] overall
SQL状态:42804
提示:将非递归项的输出转换为正确的类型。
字符:81
SQL state: 42804 Hint: Cast the output of the non-recursive term to the correct type. Character: 81
我的代码(基本上与以前的帖子相同):
my code (basically same as previous post):
WITH RECURSIVE search_graph(path, last_profile1, last_profile2) AS (
SELECT ARRAY[id], id, id
FROM node WHERE id = '408d6b12-d03e-42c2-a2a7-066b3c060a0b'
UNION ALL
SELECT sg.path || m.toid || m.fromid, m.fromid, m.toid
FROM search_graph sg
JOIN rel m
ON (m.fromid = sg.last_profile2 AND NOT sg.path @> ARRAY[m.toid])
OR (m.toid = sg.last_profile1 AND NOT sg.path @> ARRAY[m.fromid])
)
SELECT DISTINCT unnest(path) FROM search_graph;
推荐答案
尝试投射 SELECT
以递归和非递归方式列出 varchar
。
Try casting the SELECT
lists in the recursive and non-recursive terms to varchar
.
WITH RECURSIVE search_graph(path, last_profile1, last_profile2) AS (
SELECT ARRAY[id]::varchar[], id::varchar, id::varchar
FROM node WHERE id = '408d6b12-d03e-42c2-a2a7-066b3c060a0b'
UNION ALL
SELECT (sg.path || m.toid || m.fromid)::varchar[], m.fromid::varchar, m.toid::varchar
FROM search_graph sg
JOIN rel m
ON (m.fromid = sg.last_profile2 AND NOT sg.path @> ARRAY[m.toid])
OR (m.toid = sg.last_profile1 AND NOT sg.path @> ARRAY[m.fromid])
)
SELECT DISTINCT unnest(path) FROM search_graph;
这篇关于Postgres CTE:非递归术语中的字符类型为variant(255)[],但总体而言字符类型为variable []的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!