Hi.
i use postgresql 9.6.3.
I have made a small graph with nodes and edges. Each edge has a distance numeric (7,3) attribute.
when i create a recursive query and try to add the distances i get a message:
ERROR: recursive query "edges" column 3 has type numeric(7,3) in non-recursive term but type numeric overall.
if i alter the column in the edges table to integer, this message will not occur.
but if i try to cast all references to the distance to the type numerc(7,3) it still does not work.
it is an almost identical case as
https://www.postgresql.org/message-id/E1UEqGY-0000Qp-Po%40wrigleys.postgresql.org
the autor there writes it is solvable, it seems not to me.
Any idea how to solve it?
if it is a known bug, please add it in to the documents in section
https://www.postgresql.org/docs/9.6/static/queries-with.html
My exercise is almost identical to the example in the docs:
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
SELECT g.id, g.link, g.data, 1,
ARRAY[g.id],
false
FROM graph g
UNION ALL
SELECT g.id, g.link,
sg.data + g.data, -- altered section, data is numeric(7,3)
sg.depth + 1,
path || g.id,
g.id = ANY(path)
FROM graph g, search_graph sg
WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;
only the column data would be called distance and be of type numeric(7,3).
many thanks in advance
Karl
