Postgresql - subtracting current year from integer field to populate a calculated field

Question

Using PostgreSQL 12. I have been working on this for longer than I care to admit. I am needing to subtract an integer field (ex. 1960) from the current year (as an int) and have the results put into a calculated field. I have worked out the following select statement which does return the correct answer.

.

When adding the select statement as a 'generate always' the following error is thrown.

subtracting two integers into a calculated field should be fairly simple...and I am lost as to why this is not working....

Answer 1

Since PostgreSQL currently implements only stored generated columns (immutable) and not virtual (mutable, as needed for your particular case), considering that a virtual generated column is computed when it is read and also a virtual generated column is similar to a view.

Then you could implement your calculated column as a view like this:

create view amp.amp_pumpstations_vw as 
select *, 
date_part('year', current_date)- inservice as asset_age
from amp.amp_pumpstations ap;