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