PostgreSQL with-delete “relation does not exists”

问题:

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

So, my query looks like that:

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

But it shows me

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!

回答1:

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.

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)

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

回答2:

I know, you are asking how you can solve your problem using the WITH statement, and got a good answer already. But I suggest looking at alternatives in the same question you linked.

What about this one?

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

回答3:

For me it worked Like this in 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);