我正在使用postgresql 9.1,我想使用这个提示从我的表中删除重复项：
https://stackoverflow.com/a/3822833/2239537
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
但是这个说法很好：
WITH cte
AS (SELECT ROW_NUMBER()
OVER (PARTITION BY code,parent_id
ORDER BY id DESC) RN
FROM merchantcard)
SELECT * FROM cte
WHERE RN > 1
任何想法如何让它工作？
谢谢！
这是因为Postgresql中的CTE与sql Server中的CTE不同.在sql Server中,CTE就像一个可更新的视图,所以你可以从它们中删除或更新它们,在Postgresql中你不能.
你可以加入cte和delete,如：
with cte as (
select
id,row_number() over(partition by code,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语句(参见documentation),这可能非常方便.例如,您可以从卡中删除所有行,然后仅插入具有row_number = 1的行：
with cte1 as (
delete
from card
returning *
),cte2 as (
select
row_number() over(partition by code,parent_id order by id desc) as rn,*
from cte1
)
insert into card
select <columns here>
from cte2
where rn = 1
