Postgres add column with initially calculated values

Question

I'm looking to add a new column to a pre-existing table which is filled with values. The new column is going to be NOT NULL, and so for each pre-existing row it will need a value.

I'm looking for the initial values on this column to be calculated based off other values in the table at the time of column creation, and only at the time of column creation.

I have a very specific use case, so I'm not looking for a workaround. I'll give a very boiled-down example of what I'm looking for:

Say I have this data:

CREATE TABLE numbers (
  value1 INTEGER NOT NULL,
  value2 INTEGER NOT NULL
);

INSERT INTO numbers(value1, value2) VALUES (10, 20), (2, 5);

I wish to create a new column value3 on the numbers table which, at the time of creation, is always equal to the sum of its corresponding value1 and value2 columns.

E.g.:

ALTER TABLE numbers ADD COLUMN value3 INTEGER;

/* ... some more logic which calculates the initial values ... */

ALTER TABLE numbers
ALTER COLUMN value3 SET NOT NULL;

And after this is done, I'd like the following data:

-- The 3rd value will be the sum of the first 2 values
SELECT * FROM numbers;

value1 | value2 | value3
-------+--------+-------
10     | 20     | 30
2      | 5      | 7

I'll later need to update the data, perhaps ruining the relationship value3 === (value1 + value2):

UPDATE numbers SET value3=9823 WHERE value1=10;

How can I implement the step of inserting calculated initial values into the value3 column?

Answer 1

I discovered a simple way! The following adds the value3 column with the desired initial values:

ALTER TABLE numbers
ADD COLUMN value3 INTEGER; -- Exclude the NOT NULL constraint here

UPDATE numbers SET value3=value1+value2; -- Insert data with a regular UPDATE

ALTER TABLE numbers
ALTER COLUMN value3 SET NOT NULL; -- Now set the NOT NULL constraint

This method is good when postgres has a native function for the calculation you want to apply to the new column. E.g. in this case the calculation I want is "sum", and postgres does that via the + operator. This method will be more complex for operations not natively provided by postgres.

Answer 2

You have two options to do so:

1. If you need the computed value to be stored on database a trigger.

   CREATE OR REPLACE FUNCTION sum_columns()
   RETURNS trigger AS
   $BODY$
   BEGIN
   NEW.value := new.value1+new.value2;
   RETURN NEW;
   END;
   $BODY$
   LANGUAGE plpgsql;
   
   CREATE TRIGGER calculated_colum AFTER INSERT OR UPDATE ON numbers
   FOR EACH ROW EXECUTE PROCEDURE sum_columns();
   

2. If you don't need the computed value to be stored on database you can use a functional index

   CREATE INDEX sum_columns_idx ON numbers ((value1+value2));