如何在PostgreSQL中将另一张表的聚合结果存储为目标表的生成列

首先需要明确：PostgreSQL 原生的生成列仅支持基于当前行字段计算，无法依赖其他表的聚合结果，因此不能直接用GENERATED ALWAYS AS语法实现跨表的点赞数预存，以下是生产环境常用的两种替代实现方案：

该方案通过监听点赞表的变动，自动更新帖子表的点赞数字段，性能最高，完全匹配你的排序查询需求。

1. 先给Post表新增点赞数字段

ALTER TABLE Post ADD COLUMN like_count INT DEFAULT 0;

2. 创建触发器执行函数，处理点赞表变动后的点赞数同步逻辑

CREATE OR REPLACE FUNCTION update_post_like_count()
RETURNS TRIGGER AS $$
BEGIN
  -- 新增点赞时对应帖子点赞数+1
  IF TG_OP = 'INSERT' THEN
    UPDATE Post SET like_count = like_count + 1 WHERE post_id = NEW.post_id;
    RETURN NEW;
  -- 删除点赞时对应帖子点赞数-1
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE Post SET like_count = like_count - 1 WHERE post_id = OLD.post_id;
    RETURN OLD;
  -- 修改点赞关联的帖子ID时，同步调整两个帖子的点赞数
  ELSIF TG_OP = 'UPDATE' THEN
    IF OLD.post_id != NEW.post_id THEN
      UPDATE Post SET like_count = like_count - 1 WHERE post_id = OLD.post_id;
      UPDATE Post SET like_count = like_count + 1 WHERE post_id = NEW.post_id;
    END IF;
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;

3. 给Like表绑定触发器（注意Like是SQL关键字，需要用双引号包裹）

CREATE TRIGGER trigger_like_change
AFTER INSERT OR DELETE OR UPDATE OF post_id ON "Like"
FOR EACH ROW EXECUTE FUNCTION update_post_like_count();

4. 初始化存量数据的点赞数

UPDATE Post p
SET like_count = COALESCE((SELECT COUNT(*) FROM "Like" l WHERE l.post_id = p.post_id), 0);

• 优势：点赞数预存储，查询时无需额外计算，排序性能极高；触发器自动维护无需业务代码介入，数据一致性强。
• 注意：如果需要批量导入历史点赞数据，可以临时禁用触发器，导入完成后统一初始化点赞数再重新启用，避免批量操作时触发器频繁执行降低效率。

如果可以接受点赞数有分钟级/小时级的延迟，可通过物化视图实现，代码更简单：

1. 创建预计算点赞数的物化视图

CREATE MATERIALIZED VIEW post_like_stats AS
SELECT post_id, COUNT(*) AS like_count
FROM "Like"
GROUP BY post_id;

2. 查询时关联Post表和物化视图即可获取点赞数，需要更新数据时执行刷新操作：

REFRESH MATERIALIZED VIEW post_like_stats;

• 优势：实现成本低，无需编写触发器逻辑，适合点赞数更新频率低、对实时性要求不高的业务场景。

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