无索引或ID的PostgreSQL stock表删除datetime重复行的方法

场景说明 #

使用PostgreSQL，现有stock表无主键或索引，字段包含open、high、low、close、volume、datetime，表中存在datetime字段重复的行，需删除重复行，每个datetime仅保留一行。

原表数据 #

期望输出 #

正确SQL写法 #

方法1：直接删除重复行（适合不重建表的场景）

利用窗口函数标记重复行，删除编号大于1的重复记录：

WITH ranked_stock AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY datetime ORDER BY (SELECT NULL)) AS rn
    FROM stock
)
DELETE FROM stock
USING ranked_stock
WHERE stock.open = ranked_stock.open
  AND stock.high = ranked_stock.high
  AND stock.low = ranked_stock.low
  AND stock.close = ranked_stock.close
  AND stock.volume = ranked_stock.volume
  AND stock.datetime = ranked_stock.datetime
  AND ranked_stock.rn > 1;

说明：因为表无主键，需要通过所有字段匹配定位行；PARTITION BY datetime按时间分组，ROW_NUMBER()给每组内的行编号，编号>1的即为重复行。

方法2：重建去重表（适合重复行字段完全一致的场景）

如果重复行的所有字段值都相同（如示例数据），可以直接创建去重后的新表替换原表，效率更高：

-- 创建去重后的临时表
CREATE TABLE stock_temp AS
SELECT DISTINCT ON (datetime) *
FROM stock
ORDER BY datetime, (SELECT NULL); -- 第二个排序字段仅满足语法要求，不影响结果

-- 删除原表
DROP TABLE stock;

-- 重命名临时表为原表名
ALTER TABLE stock_temp RENAME TO stock;

说明：DISTINCT ON (datetime)会保留每个datetime对应的第一行，由于重复行字段完全一致，保留任意一行均可。

注意事项 #

• 操作前请务必备份数据，避免误删；
• 若后续需避免重复数据，建议给datetime字段添加唯一约束：ALTER TABLE stock ADD CONSTRAINT unique_datetime UNIQUE (datetime);

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