Arrays, casting and "constrained" data types

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

Browse pgsql-general by date

  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