PostgreSQL中如何更新视图的自定义空列？

核心原因 #

你视图中的buy是虚拟计算列（没有对应底层物理表的字段），视图本身不存储数据，数据库无法识别该列的更新目标，因此报错cannot update column "buy" of view。

解决方案分两种场景： #

场景1：需要持久化buy标记（推荐）

如果buy是需要长期保存的客户属性，直接在底层客户表新增字段，让视图引用该字段即可：

1. 给底层客户表添加buy列：

ALTER TABLE customers ADD COLUMN buy BOOLEAN;

2. 重建视图，让buy直接映射到底层表字段：

CREATE OR REPLACE VIEW active_customers AS
SELECT phone, name, location, buy
FROM customers
WHERE is_active = TRUE;

3. 现在可以直接更新视图的buy列：

UPDATE active_customers SET buy = TRUE WHERE phone = '13xxxxxxxxx';

场景2：无需持久化/不想修改底层表

如果buy是临时标记或不能改动原表，用INSTEAD OF触发器拦截视图更新，将值存储到单独的关联表中：

1. 创建存储标记的辅助表：

CREATE TABLE customer_buy_flags (
    phone VARCHAR PRIMARY KEY,
    buy BOOLEAN
);

2. 修改视图，通过左关联引入buy列：

CREATE OR REPLACE VIEW active_customers AS
SELECT c.phone, c.name, c.location, cb.buy
FROM customers c
LEFT JOIN customer_buy_flags cb ON c.phone = cb.phone
WHERE c.is_active = TRUE;

3. 创建触发器函数和触发器，拦截视图更新操作：

-- 触发器函数：处理更新逻辑
CREATE OR REPLACE FUNCTION handle_buy_update()
RETURNS TRIGGER AS $$
BEGIN
    -- 存在则更新，不存在则插入
    INSERT INTO customer_buy_flags (phone, buy)
    VALUES (NEW.phone, NEW.buy)
    ON CONFLICT (phone) DO UPDATE SET buy = NEW.buy;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定到视图的UPDATE操作
CREATE TRIGGER trigger_active_customers_buy
INSTEAD OF UPDATE ON active_customers
FOR EACH ROW
WHEN (NEW.buy IS NOT NULL)
EXECUTE FUNCTION handle_buy_update();

4. 现在更新视图的buy列时，会自动将值写入customer_buy_flags表，视图查询时能同步显示最新标记。

注意事项 #

• 不同数据库（MySQL/SQL Server/PostgreSQL）的触发器语法略有差异，上述示例基于PostgreSQL，其他数据库需调整触发器定义逻辑。
• 如果视图包含聚合、DISTINCT或复杂JOIN，即使有底层字段也可能无法直接更新，此时触发器仍是通用解决方案。

内容的提问来源于stack exchange，提问作者Jaymes