postgresql Convert array type from a non recurcive terme

Question

I am working on a recurcive query and I work on a guid stocked in a varchar(90) column. But I got an cast type exception from pgAdmin when working with Postgres 11.1

I'm stuck and I don't know what could i do to solve this, if any one can help me.

https://docs.postgresql.fr/8.4/queries-with.html

Postgres CTE : type character varying(255)[] in non-recursive term but type character varying[] overall

with recursive sousSujet(typerefin,coderefin,typerefout,coderefout,type,profondeur,chemin, boucle) as (

    SELECT p.typerefin, p.coderefin, p.typerefout , p.coderefout , p.type,1, ARRAY[p.coderefout]::varchar(90)[] ,false  
    FROM relation p
    WHERE p.coderefin='2019094070' and p.typerefin='PROJET'

    --union all     
    union

    SELECT p.typerefin, p.coderefin, pr.typerefout, pr.coderefout, p.type ,pr.profondeur+1,
        --chemin || pr.coderefout,pr.coderefout = ANY(pr.chemin)
        chemin::varchar(90)[] || pr.coderefout,pr.coderefout = ANY(pr.chemin::varchar(90)[])
    FROM relation p,sousSujet pr
    -- WHERE p.coderefin = pr.coderefout    
    WHERE p.coderefin = pr.coderefout AND NOT pr.boucle and pr.profondeur < 10
)
SELECT typerefin,coderefin,typerefout,coderefout,type,profondeur
FROM sousSujet 
order by coderefout limit 20

ERROR:  ERREUR:  dans la requête récursive « soussujet », la colonne 7 a le type character varying(90)[] dans le terme non
récursif mais le type global character varying[]
LINE 3: ...oderefin, p.typerefout , p.coderefout , p.type,1, ARRAY[p.co...
                                                             ^
HINT:  Convertit la sortie du terme non récursif dans le bon type.

user330315 · Accepted Answer · 2019-10-11 11:13:38Z

1

The easiest is to cast everything to text, by using ARRAY[p.coderefout::text] in the anchor query and chemin::text || pr.coderefout in the recursive query. There is no need to cast everything to an array

with recursive sousSujet(typerefin,coderefin,typerefout,coderefout,type,profondeur,chemin, boucle) as (

    SELECT p.typerefin, p.coderefin, p.typerefout, p.coderefout, p.type, 1,
           ARRAY[p.coderefout::text], false  
    FROM relation p
    WHERE p.coderefin='2019094070' and p.typerefin='PROJET'

    union all 

    SELECT p.typerefin, p.coderefin, pr.typerefout, pr.coderefout, p.type ,pr.profondeur+1,
           chemin::text|| pr.coderefout,
           pr.coderefout = ANY(pr.chemin)
    FROM relation p
      join sousSujet pr 
        on p.coderefin = pr.coderefout 
       AND NOT pr.boucle 
       and pr.profondeur < 10
)
SELECT typerefin,coderefin,typerefout,coderefout,type,profondeur
FROM sousSujet 
order by coderefout limit 20

answered Oct 11, 2019 at 11:13

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Dimitri Sigiscar Over a year ago

Thanks for your answer, its basicaly the cleanest query that I wanted to reach. and finaly the cleanest thing I could do is to cast theses two elements ` - ARRAY[p.coderefout]::varchar(90)[] - (chemin|| pr.coderefout)::varchar(90)[] ` "::text" don't work but as you said I don't need to cast other. thanks you

Laurenz Albe · Accepted Answer · 2019-10-11 11:30:03Z

0

The error complains that the term

ARRAY[p.coderefout]::varchar(90)[]

in the non-recursive part has type varchar(90)[], but the corresponding expression

chemin::varchar(90)[] || pr.coderefout

in the recursive part has type varchar[].

This is because the array concatenation operator || removes the type modifier:

SELECT '{a,b,c}'::varchar(90)[] || 'd'::varchar(90) \gdesc

  Column  |        Type         
----------+---------------------
 ?column? | character varying[]
(1 row)

You's have to add an explicit cast to the result of the concatenation:

(chemin || pr.coderefout)::varchar(90)[]

To avoid annoyances like that, don't use type modifiers there. I'd just use varchar or shorter, but synonymous, text. You are not using the column in the final result anyway.

answered Oct 11, 2019 at 11:30

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

1 Comment

Dimitri Sigiscar Over a year ago

Thank you for this beautiful explanation, I understand something and it works, thank you again!

Collectives™ on Stack Overflow

postgresql Convert array type from a non recurcive terme

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related