How to compute a derived age attribute in postgresql?

Question

I have the following person table with the dbirth column (date of birth). I need to compute the column age. I has been trying, but I have the following ERROR: generation expression is not immutable. I would greatly appreciate any help.

     CREATE TABLE  person 
(
  person_id   SERIAL NOT NULL,
  fname       VARCHAR(50) NOT NULL,
  lname       VARCHAR(50) NOT NULL,
  ssn         CHAR(10) NOT NULL,
  pnumber     CHAR(12) NOT NULL,
  dbirth      DATE NOT NULL,
  age         integer GENERATED ALWAYS AS  ( extract( year FROM CURRENT_DATE ) -  extract( year FROM   dbirth)) STORED,
  address_id  INTEGER NOT NULL,
  sex_id      INTEGER NOT NULL,
  PRIMARY KEY (  person_id  ),
  FOREIGN KEY (  sex_id  ) REFERENCES  sex  (  sex_id  ),
  FOREIGN KEY (  address_id  ) REFERENCES  address  (  address_id  )
);

Answer 1

You do not want a generated column for age. Postgres only supports STORED generated columns, the problem comes in that the age column is NOT update unless dbirth is updated.

  -- setup
  create or replace function get_age( from_date timestamptz )
     returns interval
     language sql immutable 
     as $$
         select age(from_date);
      $$;
    
  create table  test_person 
         ( person_id   serial  
         , fname       varchar(10) not null
         , pnumber     varchar(12) not null
         , dbirth      timestamptz(6) not null
         , age         interval generated always as (get_age(dbirth)) stored
         );
    
    insert into test_person(fname,pnumber,dbirth)
         values ('MSam', '555-937-9292', '1995-06-23 08:00:01.344612'::timestamptz )
              , ('WSam', '555-937-9292', '2019-03-21 15:32:18.863452'::timestamptz );
    commit ;

select p.fname, p.pnumber,dbirth, p.age, current_timestamp 
  from test_person p 
 order by fname;

|fname     |pnumber     |dbirth             |age                         |current_timestamp  |
|----------|------------|-------------------|----------------------------|-------------------|
|MSam      |555-937-9292|1995-06-23 03:00:01|25 years 24 days 15:59:58.65|2020-07-18 17:53:14|
|WSam      |555-937-9292|2019-03-21 10:32:18|1 year 3 mons 27 days 08:27:|2020-07-18 17:53:14|

Now wait some time, does not have to be long, but noticeable.
Then run the exact same query.   
 
select p.fname, p.pnumber,dbirth, p.age, current_timestamp 
  from test_person p 
 order by fname;

|fname     |pnumber     |dbirth             |age                         |current_timestamp  |
|----------|------------|-------------------|----------------------------|-------------------|
|MSam      |555-937-9292|1995-06-23 03:00:01|25 years 24 days 15:59:58.65|2020-07-18 17:55:29|
|WSam      |555-937-9292|2019-03-21 10:32:18|1 year 3 mons 27 days 08:27:|2020-07-18 17:55:29|

Notice in the above that although the current timestamp has changes (time has passed) the age has not. Have we just discovered "the Fountain Of Youth" So try a likely update, like phone number.

update test_person
   set pnumber = '555-949-0070' 
  where fname = 'MSam';
  
select p.fname, p.pnumber,dbirth, p.age, current_timestamp 
  from test_person p 
 order by fname;

|fname     |pnumber     |dbirth             |age                         |current_timestamp  |
|----------|------------|-------------------|----------------------------|-------------------|
|MSam      |555-949-0070|1995-06-23 03:00:01|25 years 24 days 15:59:58.65|2020-07-18 17:57:16|
|WSam      |555-937-9292|2019-03-21 10:32:18|1 year 3 mons 27 days 08:27:|2020-07-18 17:57:16|

---

Perhaps a better solution would be:

1. Drop the Age column from the table completely.
2. Create a view having and have it generate the Age.
3. Write your queries against the view.

 alter table test_person drop column age;
 create view test_person_v as 
      ( select t.*,  age( current_timestamp, dbirth) age
          from test_person t
      );
  );
      
select p.fname, p.pnumber,dbirth, p.age, current_timestamp 
  from test_person_v p 
 order by fname;
         
|fname     |pnumber     |dbirth             |age                         |current_timestamp  |
|----------|------------|-------------------|----------------------------|-------------------|
|MSam      |555-949-0070|1998-06-23 03:00:01|22 years 25 days 15:37:45.54|2020-07-18 18:37:46|
|WSam      |555-937-9292|2019-03-21 10:32:18|1 year 3 mons 28 days 08:05:|2020-07-18 18:37:46|
 
select p.fname, p.pnumber,dbirth, p.age, current_timestamp 
  from test_person_v p 
 order by fname; 
 
|fname     |pnumber     |dbirth             |age                         |current_timestamp  |
|----------|------------|-------------------|----------------------------|-------------------|
|MSam      |555-949-0070|1998-06-23 03:00:01|22 years 25 days 15:42:50.78|2020-07-18 18:42:52|
|WSam      |555-937-9292|2019-03-21 10:32:18|1 year 3 mons 28 days 08:10:|2020-07-18 18:42:52|