delete query using CTE

[Roger Bos <roger.bos@xxxxxxxxx>]

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