Thread: [GENERAL] cast issue in WITH RECURION
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
-------------------------------------------- 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.
> On 3 Aug 2017, at 20:22, k b <k_b0000@yahoo.se> wrote: > > 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. > 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; I believe the solution is rather simple; just cast(sg.data + g.data to numeric(7,3)) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
> 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. > 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; I believe the solution is rather simple; just cast(sg.data + g.data to numeric(7,3)) Alban Hertroys ---------- Tried that and it did not work. cast(sg.data + g.data AS numeric(7,3)) but the same error is produced, even if i cast the g.data in the non-recursive section.Same issue if i cast each column individually. Karl
> 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. > 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; I believe the solution is rather simple; just cast(sg.data + g.data to numeric(7,3)) Alban Hertroys ---------- Please ignore that last message from me, it does work. Thank you for the answer! Karl