How to delete rows with conditions postgres

user_19415105 0
Viewed 47

I have a postgres table with multiple rows that have one item in common. How can I remove a row if it was true or none on the table

id email verified
1 jason@gmail.com true
2 jason@gmail.com none
3 benita@outlook.com none
4 benita@outlook.com none

Expected Result:

id email verified
1 jason@gmail.com true
3 benita@outlook.com none
sql-delete postgresql sql
1 Answers

You use a window function to select the rows you want to keeo

The ORDER BY chooses true over none, so that veried emails are choosen as row number 1 if both exist., the second sorting column is id which will get the lowest id, if two or more equal emails exist.

But you should redesign your table and mal email unique, so that multiple identical emails can't be inserted

CREATE TABLE tab1
    ("id" int, "email" varchar(18), "verified" varchar(4))
;
    
INSERT INTO tab1
    ("id", "email", "verified")
VALUES
    (1, 'jason@gmail.com', 'true'),
    (2, 'jason@gmail.com', 'none'),
    (3, 'benita@outlook.com', 'none'),
    (4, 'benita@outlook.com', 'none')
;

DELETE FROM tab1
WHERE "id" IN 
(SELECT "id"
FROM (SELECT "id"
, ROW_NUMBER() OVER (PARTITION BY "email" ORDER BY "verified" DESC, "id") rn FROM tab1) t1
WHERE rn > 1)

2 rows affected

SELECt * FROM tab1

id | email              | verified
-: | :----------------- | :-------
 1 | jason@gmail.com    | true    
 3 | benita@outlook.com | none

db<>fiddle here

user_51935360
Related