How to derive an attribute value automatically based on other table values

Question

I have some tables and want to populate a database attribute based on other table interval values.

The base idea is to populate the 'eye-age' attribute with the values: young, pre-prebyotic, or prebyotic depending on patient's age.

I have the patient table birthdate, and need to populate last attribute with a value from BirthToEyeAge based on patient birthdate, inferring its age.

How can I do this, or which documentation should I read to learn these types of things.

INSERT INTO BirthToEyeAge(bId, minAge , maxAge , eyeAge)        
VALUES(1, 0, 28 , 'young')
VALUES(2, 29, 59, 'probyotic')
VALUES(3, 60, 120, 'pre-probyotic')

INSERT INTO Patient( patId, firstName, lastName, birthDate )
VALUES( 1, 'Ark', 'May', '1991-7-22' );

INSERT INTO Diagnostic( diagId, date, tear_rate, consId_Consulta, eyeAge )
VALUES( 1, '2019-08-10', 'normal', 1, ??? );

Answer 1

You can join table Patient with BirthToEyeAge, taking advantage of handy postgres function age() to compute the age of the patient at the time he was diagnosed. Here is an an insert query based on this logic:

insert into Diagnostic( diagId, date, tear_rate, consId_Consulta, eyeAge )
select d.*, b.bId
from
    (select 1 diagId, '2018-08-10'::date date, 'normal' tear_rate, 1 consId_Consulta ) d
    inner join patient p 
        on d.consId_Consulta = p.patId
    inner join BirthToEyeAge b 
        on extract(year from age(d.date, p.birthDate)) between b.minAge and b.maxAge;

In this demo on DB Fiddle, after creating the tables, initializing their content, and running the above query, the content of Diagnostic is:

| diagid | date                     | tear_rate | consid_consulta | eyeage |
| ------ | ------------------------ | --------- | --------------- | ------ |
| 1      | 2018-08-10T00:00:00.000Z | normal    | 1               | 1      |