calculate or get price of parent items on bill of materials i want to calculate or get the price of parent items on bill of materials query
here is the fiddle-&gt; https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6
please note in the second row, betax has qty = 2 and the other parent items could behave the same
qty of the first row, always will be 1
what i need is to get the 'price' and 'subtotal' for every parent
the price will surely be null if you tell it to. Initialize the base case for the CTE with 0 instead:
with recursive cte_main as 
(
    select 'ALFA001'::character varying as id_father
         , 'ALFA001'::character varying as id_son
         , 'ALFA001' as tree 
         , 1::double precision as qty
         , 0::double precision as price
         , 0::double precision as subtotal
         , 'ALFA001'::character varying as sort
         , 0 as levell  

Second, you need to handle cases where the table contains null. You can use COALESCE for that purpose:
    union all
    select e.id_father
         , e.id_son
         , repeat('......',levell+1) || e.id_son as tree
         , e.qty
         , coalesce(e.price,0)
         , e.qty * coalesce(e.price,0) as subtotal
         , sort || ' a- ' || e.id_son::character varying as sort  
         , levell+1 
    from public.main as e
    inner join cte_main 
        on e.id_father = cte_main.id_son 
)
select * from cte_main 
order by sort

Since all your attributes may contain null, you need to take that into consideration for the other attributes as well.