--------------------------------------------
Den tors 2017-08-03 skrev k b <k_b0000@yahoo.se>:
Ämne: cast issue in WITH RECURION
Till: pgsql-general@postgresql.org
Datum: torsdag 3 augusti 2017 20:22
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
COMMENT ADDED:
changing the column to real or double precision will allow the WITH RECURSIVE to run just fine with no casting at all.
I can live with this but i would prefer to use exact data types, before inexact.
