PostgreSQL and inheritance

Question

I have a table with this format:

Table name : identities

    code   |parent_code|  
    _______|___________|
    AAA    |    Null   | 
    AAB    |    AAA    | 
    AAC    |    Null   | 
    AAD    |    AAC    | 
    AAE    |    AAB    |

And I need a way to obtain the highest parent of any "code" in the table.

For instance, if I wanted to get the highest parent of the "code" AAE I would get as a result AAA, since the parent of AAE is AAB and the parent of AAB is AAA, and AAA would be the highest because it has no parent assosiated.

My problem is that I can´t modify the tables and I don´t know how to use "with recursive".

Thank you in advance.

score 1 · Accepted Answer · answered Dec 08 '19 at 15:55

You can use a recursive cte as follows:

with recursive cte as (
    select code, parent_code, 1 lvl from identities where code = 'AAE'
    union all
    select i.code, i.parent_code, lvl + 1
    from identities i
    inner join cte c on c.parent_code = i.code 
)
select code 
from cte
where lvl = (select max(lvl) from cte)

Demo on DB Fiddle:

| code |
| :--- |
| AAA  |

PostgreSQL and inheritance

1 Answers1