Arrays, casting and "constrained" data types

Поиск
Список
Период
Сортировка
От Thomas Kellerer
Тема Arrays, casting and "constrained" data types
Дата
Msg-id 693a9f48-07ce-ce8b-d4e6-c890b36aadcf@gmx.net
обсуждение исходный текст
Список 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






В списке pgsql-general по дате отправления:

Предыдущее
От: Naveen Dabas
Дата:
Сообщение: Re: pg_sample
Следующее
От: Ravi Krishna
Дата:
Сообщение: Re: pg_sample