I hope that title is clear. If not, please change.
I have table, which look like this:
I would like to create a select query, where if gid is in anywhere in ancestor column, select max level from this rows. For example: Gid is 727 is four times in ancestor column. Max level from this rows is 3, so the value in the new column, let's says level_a, where gid is 727, will be 3. For gid 725 the same but for gid 717 will be 0.
I tried to use unnest, join, subquery but nothing worked. I have read about lateral join but I don't have any idea, how I can use it.
I'm using PostgreSQL 10 with PostGIS 2.5.2