PostgreSQL带有DELETE语句时提示"relation does not exist"错误

Question

我正在使用postgreSQL 9.1，并想使用以下提示从我的表中删除重复项：https://dev59.com/dXVD5IYBdhLWcg3wU56H#3822833

因此，我的查询看起来像这样：

WITH cte
 AS (SELECT ROW_NUMBER() 
 OVER (PARTITION BY code, card_id, parent_id 
     ORDER BY id DESC) RN
     FROM card)
DELETE FROM cte
WHERE RN > 1

但它向我展示了

ERROR: relation "cte" does not exist
SQL state: 42P01
Character: 157

然而这个语句运行良好：

WITH cte
 AS (SELECT ROW_NUMBER() 
 OVER (PARTITION BY code, card_id, parent_id 
     ORDER BY id DESC) RN
     FROM merchantcard)
SELECT * FROM cte
WHERE RN > 1

你有什么想法可以让它工作吗？ 谢谢！

- Alex Kartishev

Answer 1

这是因为PostgreSQL中的公共表表达式（CTE）与SQL Server中的不同。在SQL Server中，CTE类似于可更新的视图，可以从中删除或更新数据，而在PostgreSQL中则不能。

您可以使用联接语句和DELETE命令来操作CTE，例如：

with cte as (
    select
        id,
        row_number() over(partition by code, card_id, parent_id order by id desc) as rn
    from card
)
delete
from card
where id in (select id from cte where rn > 1)

另一方面，在PostgreSQL中，您可以在CTE中编写DDL语句（请参阅文档），这非常方便。例如，您可以删除card中的所有行，然后仅插入那些具有row_number = 1的行：

with cte1 as (
    delete
    from card
    returning *
), cte2 as (
    select
        row_number() over(partition by code, card_id, parent_id order by id desc) as rn,
        *
    from cte1
)
insert into card
select <columns here>
from cte2
where rn = 1

- Roman Pekar

---

哇，谢谢你的快速回复和有用的答案！对我很有帮助，只需要添加“delete FROM card”，但这只是一个打字错误，当然。 - Alex Kartishev

第一种解决方案似乎会删除表中的所有行。我不理解cte的行为：如果我执行select count(1) from cte where rn > 1，我会得到正确的数字，但是select count(1) from card where id in (select id from cte where rn > 1)会返回所有行。 - Arkadiy Kukarkin

@ArkadiyKukarkin，可能id不是唯一的吗？为了帮助您，最好提供有关您的结构和想要获得的结果的更多信息。 - Roman Pekar

点赞！这也适用于多列吗？ 我有一个情况，从表中删除 where (a,b) in (select a,b from cte)，其中a,b在我的表中形成复合关键字。 - PirateApp

这个可以用来在Postgres中去重一个表。 哇，我是从尝试删除Postgres中完全重复的行而来的，差点放弃了，然后发现这个黄金组合了我在其他地方找到的东西，以一种有效的方式工作。谢谢！ - canada11

Answer 2

我知道您正在询问如何使用WITH语句解决问题，并已经得到了很好的答案。但我建议您查看您链接的同一问题中的替代方案。

这个怎么样？

DELETE FROM card
WHERE id NOT IN (
  SELECT MIN(id) FROM card
  GROUP BY code, card_id, parent_id 
);

- maf-soft

---

这是我见过的最清晰的在PostgreSQL中检测和清除重复记录的方法。谢谢。 - Carl Zulauf

那么为什么我会得到这么多踩？不告诉我是不公平的。 - maf-soft

可能是因为您的解决方案需要已经存在一个可用于where子句的唯一键（id），而原始问题并没有其中之一。 - Jamie Cook

Answer 3

在Postgres/GreenPlum中，我是这样做的：

delete
from card where id in (
with cte as (
    select
        id,
        row_number() over(partition by code, card_id, parent_id order by id desc) as rn
    from card
)
select id from cte where rn > 1);

- Yatendra Yadav