PostgreSQL with-delete "relation does not exists"

Question

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!

Alex Kartishev · Accepted Answer · 2013-08-26 08:04:52Z

41

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

edited Aug 26, 2013 at 8:04

Alex Kartishev

1,8663 gold badges18 silver badges26 bronze badges

answered Aug 26, 2013 at 7:45

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alex Kartishev Over a year ago

Wow, thanks for the quick reply and useful answer! Worked for me, only had to add "delete FROM card", but this is just typo, of course.

Arkadiy Kukarkin Over a year ago

The first solution appears to delete all rows in the table for me. I don't understand cte behavior: if I do select count(1) from cte where rn > 1 I get the correct number, but select count(1) from card where id in (select id from cte where rn > 1) returns all rows

roman Over a year ago

@ArkadiyKukarkin could it be that id is not unique? to help you it's better to have more information about your structure and result you want to get

PirateApp Over a year ago

upvoted! does this work with multiple columns as well i have a case, delete from table where (a,b) in (select a,b from cte) a,b form a composite key in my table

liquid_diamond Over a year ago

This works to dedupe a table in Postgres. Wow, I made my way here from trying to delete full duplicate rows in Postgres and was about to give up, then found this gold that combined other stuff I found in a way that works. Thanks!

maf-soft · Accepted Answer · 2013-10-24 07:38:40Z

11

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 
);

answered Oct 24, 2013 at 7:38

maf-soft

2,5623 gold badges28 silver badges57 bronze badges

3 Comments

Carl Zulauf Over a year ago

This is the clearest way I've seen to detect and clean duplicate records in PostgreSQL. Thank you.

maf-soft Over a year ago

So why do I get so many downvotes? It's not fair not to tell me.

Jamie Cook Over a year ago

probably because your solution requires that there already exists a unique key (id) which can be used in the where... the original question doesn't have one of those

Yatendra Yadav · Accepted Answer · 2017-12-05 10:59:20Z

2

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);

answered Dec 5, 2017 at 10:59

Yatendra Yadav

975 silver badges9 bronze badges

Collectives™ on Stack Overflow

PostgreSQL with-delete "relation does not exists"

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related