PostgreSQL表连接去重及SQLite迁移至Heroku Postgres报错解决

首先，这个错误是PostgreSQL和SQLite对GROUP BY规则的严格程度差异导致的。SQLite有个非标准的“宽松模式”，允许你在SELECT里包含既不在GROUP BY里、也没用到聚合函数的字段——它会随便挑分组里的某一行值返回。但PostgreSQL严格遵循SQL标准，要求所有出现在SELECT中的非聚合字段，必须要么在GROUP BY子句里，要么被聚合函数（比如MAX()、MIN()、SUM()）包裹。

看你的原查询：

SELECT DISTINCT c.name, r.social, c.description, p.price 
FROM cryptomodels_coin c 
LEFT JOIN cryptomodels_coinprice p ON p.coin_id = c.name 
LEFT JOIN cryptomodels_CoinRating r ON r.coin_id = c.name 
GROUP BY c.name

这里r.social、c.description、p.price既不在GROUP BY里，也没有聚合处理，PostgreSQL自然会报错。而SQLite偷偷帮你“补全”了逻辑，但结果其实不可预测（比如不同运行可能返回不同的price值）。

解决GROUP BY错误的两种方案 #

方案1：把所有非聚合字段加入GROUP BY #

如果你的业务逻辑中，每个币种（c.name）对应的r.social、c.description、p.price都是唯一的，直接把这些字段都加到GROUP BY里就行：

SELECT c.name, r.social, c.description, p.price 
FROM cryptomodels_coin c 
LEFT JOIN cryptomodels_coinprice p ON p.coin_id = c.name 
LEFT JOIN cryptomodels_CoinRating r ON r.coin_id = c.name 
GROUP BY c.name, r.social, c.description, p.price;

不过如果关联表（比如cryptomodels_coinprice）里一个币种有多条价格记录，这样会返回多条重复的币种行，这时候就需要结合去重逻辑。

方案2：用聚合函数处理多值字段 #

如果一个币种对应多个价格/评分记录，你需要明确告诉PostgreSQL取哪个值——比如最新价格、任意一条评分。举个例子，假设要取每个币种的最新价格和任意评分：

SELECT 
  c.name, 
  MAX(r.social) AS social, -- 取该币种的任意一条评分（如果有多个的话）
  c.description, 
  MAX(p.price) AS latest_price -- 假设价格随时间递增，MAX就是最新价格
FROM cryptomodels_coin c 
LEFT JOIN cryptomodels_coinprice p ON p.coin_id = c.name 
LEFT JOIN cryptomodels_CoinRating r ON r.coin_id = c.name 
GROUP BY c.name, c.description; -- c.description和c.name一一对应，可安全加入GROUP BY

PostgreSQL中连接表避免重复数据的方法 #

要避免连接后出现重复行，核心是确保每个主表行对应唯一的关联表行，推荐这几种方法：

1. 先筛选关联表的唯一记录再连接 #

比如cryptomodels_coinprice里每个币种有多条价格，先通过CTE拿到最新价格，再和主表连接：

WITH latest_prices AS (
  SELECT 
    coin_id, 
    price,
    -- 按创建时间排序，给每个币种的价格打序号，1就是最新的
    ROW_NUMBER() OVER (PARTITION BY coin_id ORDER BY created_at DESC) AS rn
  FROM cryptomodels_coinprice
)
SELECT 
  c.name, 
  r.social, 
  c.description, 
  lp.price
FROM cryptomodels_coin c
LEFT JOIN latest_prices lp ON lp.coin_id = c.name AND lp.rn = 1
LEFT JOIN cryptomodels_CoinRating r ON r.coin_id = c.name;

2. 使用PostgreSQL特有的DISTINCT ON #

这个语法可以按指定字段去重，保留每个分组的第一行，非常适合你的场景：

SELECT DISTINCT ON (c.name)
  c.name, 
  r.social, 
  c.description, 
  p.price
FROM cryptomodels_coin c
LEFT JOIN cryptomodels_coinprice p ON p.coin_id = c.name
LEFT JOIN cryptomodels_CoinRating r ON r.coin_id = c.name
-- 按币种分组后，按价格创建时间倒序，确保取到最新的价格
ORDER BY c.name, p.created_at DESC;

DISTINCT ON (c.name)会自动保留每个币种的第一行，ORDER BY用来控制哪一行是“第一行”。

3. 清理关联表的重复数据 #

如果cryptomodels_CoinRating里一个币种有多条评分记录，连接后也会产生重复。这时候需要先清理这个表的重复数据，或者用聚合函数（比如MAX()、MIN()）筛选出需要的那条记录。

内容的提问来源于stack exchange，提问作者Ashley C.