postgresql - Postgres Query to delete duplicated rows by many parameters

Question

I have a table with fields: Guide, FileSource, CodeName. All these fields are of text type. Some fields have the same values, and sometimes the values are NULL. I made query which deletes duplicate rows with the same parameters, however, when some values equal NULL there is nothing deleted rows. How could I change the query that will delete rows with the same parameters include values equal NULL?

DELETE FROM public.TableName as T1
USING public.TableName as T2
WHERE T1.ctid > T2.ctid
AND T1."Guide" = T2."Guide"
AND T1."FileSource" = T2."FileSource"  
AND T1."CodeName" = T2."CodeName";

question from:https://stackoverflow.com/questions/66051017/postgres-query-to-delete-duplicated-rows-by-many-parameters

Answer 1

NULLs cannot be compared directly (with "="). You can handle NULLs using the coalesce statement like this:

DELETE FROM public.TableName as T1
USING public.TableName as T2
WHERE T1.ctid > T2.ctid
AND COALESCE(T1."Guide",'') = COALESCE(T2."Guide",'')
AND COALESCE(T1."FileSource",'') = COALESCE(T2."FileSource",'')
AND COALESCE(T1."CodeName",'') = COALESCE(T2."CodeName",'');