Postgresql How to get all parents (tracking) from child

Question

I got something like this table:

id  | code    | code_parent | 
----|---------|------------ |
 1  | 1       |   1         | 
 2  | 2       |   2         | 
 3  | 3       |   1         | 
 4  | 4       |   3         |  
 5  | 5       |   2         | 
 6  | 6       |   4         | 
 7  | 7       |   6         | 

I want to return a list with all the parents. Example:

select * from table where code 7

I would like the return like this:

{1,3,4,6}

Thanks for any help.

Answer 1

You can use a recursive CTE:

with recursive cte as (
      select code_parent, 1 as lev
      from t
      where code = 7
      union all
      select t.code_parent, lev + 1
      from cte join
           t
           on cte.code_parent = t.code
      where t.code <> t.code_parent
     )
select code_parent as code
from cte;

If you want an array:

select array_agg(code_parent)
from cte;

Here is a db<>fiddle.