From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Arrays, casting and "constrained" data types |
Date: | 2018-08-24 11:43:15 |
Message-ID: | 693a9f48-07ce-ce8b-d4e6-c890b36aadcf@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ravi Krishna | 2018-08-24 12:43:33 | Re: pg_sample |
Previous Message | Naveen Dabas | 2018-08-24 10:38:55 | Re: pg_sample |