Computed / calculated / virtual / derived / generated columns in PostgreSQL

Juxtaposing Views and Functions

Views and functions offer a dynamic approach to enrich the dataset beyond computed columns. Here's an example of creating a view to add tenure column:

CREATE VIEW employees_with_tenure AS
SELECT *, EXTRACT(YEAR FROM AGE(hire_date)) AS tenure FROM employees;


Let's create a function to model a virtual column:

CREATE FUNCTION tenure(employee employees) RETURNS INT AS $$
BEGIN
  RETURN EXTRACT(YEAR FROM AGE(employee.hire_date));
END;
$$ LANGUAGE plpgsql;


This function can be called using dot notation as if it's a column:

SELECT name, birthdate, employees.tenure(employees) FROM employees;


Beefing-up Performance Using Expression Indexes

Queries with computed expressions earn a speedy ticket through expression indexes:

CREATE INDEX idx_employee_age ON employees ((EXTRACT(YEAR FROM AGE(birthdate))));


Now, enjoy faster sorting and searching on age_years even though it's virtually computed.

Virtual Columns in Legacy PostgreSQL

Triggers remarkably simulate computed columns in earlier versions of PostgreSQL, distributed before 12. Peek at this example:

CREATE OR REPLACE FUNCTION compute_age()
RETURNS TRIGGER AS $$
BEGIN
  NEW.age_years := EXTRACT(YEAR FROM AGE(NEW.birthdate));  -- printing another wrinkle :)
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER age_trigger
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE compute_age();


In older versions, materialized views act as a cache, storing results of complex computations.

Using Rules for Consistency

Rules help maintain data consistency for computed values:

CREATE OR REPLACE RULE compute_insert AS
ON INSERT TO employees_with_tenure
DO INSTEAD INSERT INTO employees (name, birthdate) VALUES (NEW.name, NEW.birthdate);


Advanced Use-cases: Dealing with the Unexpected

PostgreSQL's ETL capabilities come handy while managing calculated columns. Just look at this SQL magic:

UPDATE employees e SET age_years = EXTRACT(YEAR FROM AGE(e.birthdate));


Also, dbfiddle is an excellent resource for demonstration and exploration.

Keeping Data Fresh and Tasty

The freshness of computed columns lasts as long as the last data change. Scheduled updates or triggers are useful ways to keep the numbers crispy.

Gearing Up for Future Enhancements

Stay tuned, PostgreSQL enthusiasts! VIRTUAL generated columns might be a part of the future, bringing more versatility to the table.

The Power of Documentation

PostgreSQL's extensive documentation is your ally. It's the foundation for understanding and effectively implementing computed columns.

Finding the Balance with Optimization

Weighing convenience against storage and performance costs is vital to designing an optimized database.