Is it possible to get column value with subquery based on column's key?

Question

I have the following table:

id   |     fruit |  parent |
----------------------------
id_1 |     apple |         |
id_2 |           |    id_3 |
id_3 | pineapple |         |
id_4 |      plum |    id_5 |
id_5 |      plum |         |

Is it possible with a subquery/lateral join to get the following output:

id_1 apple
id_2 pineapple
id_4 plum

So if the fruit is null, get the parent's fruit. Tried to get this with subquery, collected the linked parents to get the fruit values from that but in that case those were in pair with their ids, not with the "kids" ids. So something like this:

id_1 apple
id_3 pineapple
id_4 plum

Answer 1

If this is only a single level up, you can do it like this:

select id, 
       coalesce(fruit, (select t2.fruit
                        from the_table t2
                        where t2.id = t1.parent
                        limit 1)) as fruit
from the_table t1

Online example

Answer 2

You can do something like this:

EDIT, sorry, missed that it was postgres. This works

SELECT t1.id, (CASE WHEN t1.parent is NULL THEN t1.fruit ELSE t2.fruit END) as fruit
FROM fruits as t1
LEFT JOIN fruits as t2 ON t1.parent = t2.id;