Re: cast issue in WITH RECURION

--------------------------------------------
Den tors 2017-08-03 skrev k b <k_b0000(at)yahoo(dot)se>:

Ämne: cast issue in WITH RECURION
Till: pgsql-general(at)postgresql(dot)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.