这是因为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