delete query using CTE

You can't delete rows that are in a CTE. You want to delete rows that are
in the table. Do you have a primary key that you can reference? Else, you
may need to reference the system column ctid.

On Sun, Mar 13, 2022 at 7:44 AM Roger Bos <roger.bos@gmail.com> 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.

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.johnston@gmail.com> wrote:

Le 13/03/2022 à 15:44, Roger Bos a écrit :

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:

"US000000094541" "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
"US000000094541" "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
"US000000009823" "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
"US000000090393" "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

As Michael Lewis says, you can't use delete from cte but you can build
your request to do the work with something like (example from you
original request) :

WITH cte AS
( SELECT *
, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) my_row_num
, ctid -- or pk or other unique identifier if exists
FROM price_old
)
DELETE FROM tmp24
WHERE ctid IN (SELECT ctid FROM cte WHERE my_row_num = 1);

Benj