删除了“关系不存在”的PostgreSQL。 [英] PostgreSQL with-delete "relation does not exists"

问题描述

我正在使用PostgreSQL 9.1，并且想使用以下提示从表中删除重复项：
https：// stackoverflow .com / a / 3822833/2239537

I am using postgreSQL 9.1 and I want to delete duplicates from my table using this tip: https://stackoverflow.com/a/3822833/2239537

因此，我的查询如下：

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

但是此语句可以正常工作：

However this statement works fine:

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

有什么想法可以使它起作用吗？
谢谢！

Any ideas how to get it work? Thanks!

推荐答案

这是因为PostgreSQL中的CTE与SQL Server中的CTE工作方式不同。在SQL Server中，CTE就像是可更新的视图，因此您可以从中删除或更新它们，而在PostgreSQL中则不能。

that's because CTE in PostgreSQL works differently than CTE in SQL Server. In SQL Server CTE are like an updatable views, so you can delete from them or update them, in PostgreSQL you cannot.

您可以加入cte和删除，例如：

you can join cte and delete, like:

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的行：

On the other hand, you can write DDL statements inside CTE in PostgreSQL (see documentation) and this could be very handy. For example, you can delete all rows from card and then insert only those having 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

这篇关于删除了“关系不存在”的PostgreSQL。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！