where clause in delete statement with cte not working as expected

[Tejas360]

CREATE TABLE IF NOT EXISTS person(id integer , email text);
INSERT INTO person VALUES (1,'abc@gmail.com'),
                        (2,'abc@gmail.com'),
                         (3,'apple@gail.com');
WITH cte AS 
( 
SELECT email, ROW_NUMBER () over(PARTITION BY email) AS row_no  FROM person 
)DELETE FROM person USING cte WHERE row_no>1;

SELECT * FROM person ;

this code should only delete the rows with row_no value grater than 1 but it is deleting all the rows from a table ,how and why?
I checked with different row_no values like grater than 2 or 3 then it's working as expected not deleting a single row as no row_number grater than 2 or 3 but as soon as it is hitting 1 it is deleting all the rows

[drocha87]

I don't think this is a problem related with this libray what so ever. Your query doesn't work as expected on psql either.

I'm not a sql expert, but I don't think you're using the DELETE ... USING properly. Take a look at https://www.postgresql.org/docs/current/sql-delete.html mainly in NOTESsection.