From: | benj(dot)dev(at)laposte(dot)net |
---|---|
To: | Roger Bos <roger(dot)bos(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: delete query using CTE |
Date: | 2022-03-13 17:05:50 |
Message-ID: | 0cc10ba0-8979-a064-0714-cc5d3d50cf7b@laposte.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Uckun | 2022-03-14 05:13:39 | Indexes that would span partitions. |
Previous Message | Roger Bos | 2022-03-13 16:06:36 | Re: delete query using CTE |