delete query using CTE

[Roger Bos]

Hello, trying to use CTE to remove duplicates from a table.  The DELETE
version does not work, but the SELECT version does, so I am not
understanding what the problem is.  Any suggestions on how to fix it?

Here is my query:

WITH cte AS
( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ticker, date ORDER BY ticker,
date) my_row_num FROM price_old)
DELETE FROM cte WHERE my_row_num > 1;

I get the following error:

ERROR: relation "cte" does not exist LINE 3: DELETE FROM cte WHERE
my_row_num > 1;

But when I run change the query to a select query it runs fine (in that it
returns all the duplicate rows). For example:

WITH cte AS
( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ticker, date ORDER BY ticker,
date) my_row_num FROM price_old)
SELECT * FROM cte WHERE my_row_num > 1;

Sample output:

"US00094541" "AAC" "2022-03-08 00:00:00-05" 9.75 9.76 9.75 9.75 100215
9.75 9.76 9.75 9.75 100215 0 1 2
"US00094541" "AAC" "2022-03-09 00:00:00-05" 9.75 9.76 9.75 9.76 111334
9.75 9.76 9.75 9.76 111334 0 1 2
"US9823" "AAC" "2022-03-10 00:00:00-05" 9.75 9.76 9.74 9.74 170474
9.75 9.76 9.74 9.74 170474 0 1 2
"US00090393" "ABCL" "2022-03-08 00:00:00-05" 8.19 8.545 7.81 8.22
1984348 8.19 8.545 7.81 8.22 1984348 0 1 2

Thanks,
Roger

Re: delete query using CTE

[Roger Bos]

Thank you Michael & David for your extremely fast response.  With your help
I was able to fix the query as follows:

DELETE FROM price_old
WHERE ctid IN
(SELECT ctid
FROM
(SELECT ctid,
 ROW_NUMBER() OVER( PARTITION BY ticker, date
ORDER BY ctid ) AS my_row_num
FROM price_old ) t
WHERE t.my_row_num > 1 );


On Sun, Mar 13, 2022 at 10:52 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Sun, Mar 13, 2022 at 7:44 AM Roger Bos  wrote:
>
>> WITH cte AS
>> ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ticker, date ORDER BY
>> ticker, date) my_row_num FROM price_old)
>> DELETE FROM cte WHERE my_row_num > 1;
>>
>> I get the following error:
>>
>> ERROR: relation "cte" does not exist LINE 3: DELETE FROM cte WHERE
>> my_row_num > 1;
>>
>
> Right...when all is said and done DELETE removes rows from permanent
> tables.  While "cte" does exist it is a virtual table and so doesn't
> qualify.  A permanent relation named cte does not exist from which
> permanent data can be deleted.
>
> See the following for ways to deal with duplicate removal on incorrectly
> constrained tables.
>
>
> https://harshitjain.home.blog/2019/06/17/postgresql-how-to-delete-duplicate-rows-in-postgresql/
>
> David J.
>
>