Re: delete query using CTE

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