Postgres 添加带有初始计算值的列

Question

我想向一个已存在且填有值的表中添加一个新列。新列将会是NOT NULL，因此对于每一行已存在的行，它都需要一个值。

我希望在列创建时基于表中其他值来计算初始值，而且仅限于列创建时。

我有一个非常具体的用例，因此我不寻求解决方案。以下是我所需的简化示例：

假设我有以下数据：

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

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

我希望在“numbers”表上创建一个新的列value3，该列在创建时始终等于其对应的value1和value2列之和。

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;

完成后，我想要以下数据：

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

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

我之后需要更新数据，可能会破坏关系 value3 === (value1 + value2)：

UPDATE numbers SET value3=9823 WHERE value1=10;

我该如何实现将计算出的初始值插入到value3列的步骤？

- Gershom Maes

Answer 1

我发现了一个简单的方法！以下代码将使用所需的初始值添加value3列：

我找到了一个简单的方法！以下内容会将value3栏目加上所需的初始值：

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

当Postgres具有您要应用于新列的计算的本机函数时，此方法非常有效。例如，在这种情况下，我想要的计算是“求和”，而Postgres通过+运算符来执行该操作。对于Postgres不原生支持的操作，此方法将更加复杂。

- Gershom Maes

Answer 2

你有两个选项可以这样做：

1. 如果您需要计算值存储在数据库中，请使用触发器。

   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. 如果您不需要将计算值存储在数据库中，则可以使用函数索引。

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

- L. Amigo

---

我希望 value3 列只在创建列时进行初始计算。我认为这将在插入或更新时继续计算 value3？ - Gershom Maes

如果您只想计算一次，永远不再计算，并且不希望它被更新，请按照以下建议创建列并进行更新或更改。 - L. Amigo

对我来说，这里描述的触发器示例并没有起作用，这个回答中的示例起了作用。在我看来，主要的区别是触发器应该是BEFORE插入或更新，而不是AFTER。 - stempler