cast issue in WITH RECURION

From: k b <k_b0000(at)yahoo(dot)se>
To: <pgsql-general(at)postgresql(dot)org>
Subject: cast issue in WITH RECURION
Date: 2017-08-03 18:22:32
Message-ID: 264036359.6712710.1501784552013@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message k b 2017-08-03 19:19:29 Re: cast issue in WITH RECURION
Previous Message Jeff Janes 2017-08-03 17:58:06 Re: hot standby questions