PostgreSQL指定id删除单条重复行遇DELETE禁用LIMIT解决方案问询

• 你贴出的SQL首先存在语法错误：子查询末尾多余了一个分号，会直接导致执行失败，需要先删除该分号。
• 你需要仅针对指定id删除重复行，只要在外层DELETE的筛选条件中追加id过滤，就能实现双重校验避免误删其他id的行。

如果你需要的是保留每一组(id、lot、expiration_date)最新的扫描记录，删除旧的重复行，参考写法如下：

DELETE FROM allergen_list 
WHERE id = 'F284' -- 外层追加id过滤，避免误删
  AND date_scanned IN (
    WITH cte AS (
        SELECT date_scanned, 
               row_number() OVER(PARTITION BY id, lot, expiration_date ORDER BY date_scanned DESC) as rn
        FROM allergen_list 
        WHERE id = 'F284'
    )
    SELECT date_scanned FROM cte WHERE rn > 1 -- 若要删除最新的重复行，改回rn=1即可
);

PostgreSQL支持直接在CTE中定位待删除行，用内置唯一行标识ctid查询性能更优，写法如下：

WITH cte AS (
    SELECT ctid,
           row_number() OVER(PARTITION BY id, lot, expiration_date ORDER BY date_scanned DESC) as rn
    FROM allergen_list 
    WHERE id = 'F284'
)
DELETE FROM allergen_list 
WHERE ctid IN (SELECT ctid FROM cte WHERE rn > 1)
  AND id = 'F284';

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