-------------------------------------------- Den tors 2017-08-03 skrev k b <k_b0000@xxxxxxxx>: Ämne: cast issue in WITH RECURION Till: pgsql-general@xxxxxxxxxxxxxx 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
