What is the best way in Postgres to inherit values from parent row recursively if not provided?

Question

Is there a pattern in SQL where a child row inherits empty (null?) values from a parent?

E.g., given the following 'chickens' table (sorry):

id	parent_id	name	flys	noise	weight	egg_color	special_feathers
1		chicken	true	clucks	5lbs	white	false
2	1	maran				brown
3	1	silkie	false	squeeks	4lbs
4	3	silkie frizzle					true

I want to easily be able to do:

select * from chickens where egg_color = 'white'

and have it return all the chickens except for the maran.

I don't want to explicitly set the values in child rows because I want to be able to update the parent data and have it updated everywhere.

I think this can be accomplished via a recursive view, however I am curious if there are any other solutions.

I am also not sure how to handle explicitly empty states other than with an empty string (which won't work for non-string fields). This might not be something we need to accommodate but want to see if anyone has ideas.

Answer 1

The most clear way to implement this is to set up a trigger that, BEFORE INSERT (NOT UPDATE :)), when a new field is NULL, we look up the parent's attributes and set the new column value with those defaults. If you update and migrate away your null values oince, you should never have to do a recursive dive again. That is, instead of having a null egg color, immediately inherit the parent and then change it accordingly.

That may not be desirable.

Instead, you can indeed try a recursive query like so:

WITH RECURSIVE chickenhierarchy AS (
    SELECT c.ID
         , c.parent_id
         , c.name
         , c.egg_color
         , c.parent_id AS root_parent_id
    FROM chickens c
    WHERE c.parent_id IS NULL
    
    UNION ALL
    
    SELECT c.ID, c.parent_id, c.name, 
           COALESCE(c.egg_color, ph.egg_color) AS egg_color, 
           ph.root_parent_id
    FROM chickens c
    JOIN chickenhierarchy ph ON c.parent_id = ph.ID
)
SELECT ID, parent_id, name, egg_color
FROM chickenhierarchy;

id	parent_id	name	egg_color
1	NULL	daddy	white
2	1	randy	brown
3	1	george	white
4	3	steve	white
5	4	pete	white

The swapping of NULLs is done by the COALESCE(c.egg_color, ph.egg_color) AS egg_color, call.