PostgreSQL删除“关系不存在”

一尘不染

PostgreSQL删除“关系不存在”

sql

因此，我的查询如下所示：

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

任何想法如何使它起作用？谢谢！

阅读 138

2021-05-23

共1个答案

一尘不染

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

您可以加入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

2021-05-23