PostgreSQL with-delete "не существует"

Я использую postgreSQL 9.1, и я хочу удалить дубликаты из своей таблицы, используя этот совет: qaru.site/info/10777/...

Итак, мой запрос выглядит так:

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

Любые идеи, как заставить его работать? Спасибо!

Ответы

Ответ 1

потому что CTE в PostgreSQL работает иначе, чем CTE в SQL Server. В 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)

С другой стороны, вы можете писать заявления DDL внутри CTE в PostgreSQL (см. документация), и это может быть очень удобно. Например, вы можете удалить все строки из 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

Ответ 2

Я знаю, вы спрашиваете, как вы можете решить свою проблему, используя инструкцию WITH, и получили хороший ответ. Но я предлагаю взглянуть на альтернативы в том же вопросе, который вы связали.

Как насчет этого?

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

Ответ 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);