Search code examples
postgresqlcase-insensitiveduplicates

PostgreSQL delete duplicate rows ignoring case

I need to delete from a table rows that have the same value on a specified field ignoring case. For example if I have a row that has 'foo' as value for a field and another row that has 'Foo' as value for the same field, I want to delete only one of these rows (keeping 1 row).
I've tried something like this: 

delete from table t1 
where exists (select 1 
              from table t2 
              where t1.key <> t2.key 
                and t1.field ILIKE t2.field)

but this deletes the other row too.
Are there any suggestions?

Solution

  • Just change <> by <:

    DELETE
  FROM table t1 
 WHERE exists (
    SELECT 1 
      FROM table t2 
     WHERE t1.key < t2.key 
       and t1.field ILIKE t2.field
 )

    This way you keep the rows with the highest key. You also could use > to keep the records with the lowest key.