I stumbled across the following:
Consider the following (simplified) table:
create table test
(
val numeric(20,0),
ref_val numeric(20,0)
);
and the following very simple recursive CTE:
with recursive tree as (
select val, array[val] as path
from test
union all
select child.val, parent.path||child.val
from test child
join tree parent on parent.val = child.ref_val
)
select *
from tree;
The above fails with: recursive query "tree" column 2 has type numeric(20,0)[] in non-recursive term but type numeric[]
overall
However, when casting the array in the non-recursive part, it still doesn't work:
with recursive tree as (
select val, array[val]::numeric[] as path
from test
union all
select child.val, parent.path||child.val
from test child
join tree parent on parent.val = child.ref_val
)
select *
from tree;
same error as before. Neither does array[val::numeric] work.
However, appending the column to an empty array works:
with recursive tree as (
select val, array[]::numeric[] || val as path
from test
union all
select child.val, parent.path||child.val
from test child
join tree parent on parent.val = child.ref_val
)
select *
from tree;
My question is: why isn't "array[val]::numeric[]" enough to create a numeric[] array in the non-recursive part?
I have seen the same problem with "varchar(x)"
Thomas