Re: [GENERAL] cast issue in WITH RECURION

[k b]

  > 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cast issue in WITH RECURION

[k b]

 > 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cast issue in WITH RECURION

[Alban Hertroys]

> On 3 Aug 2017, at 20:22, k b  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.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] cast issue in WITH RECURION

[k b]

Den tors 2017-08-03 skrev k b :

 Ä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-Qp-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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general