PostgreSQL delete duplicate rows ignoring case

Question

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?

DavidEG · Accepted Answer · 2011-10-08 16:16:21Z

3

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.

answered Oct 8, 2011 at 16:16

DavidEG

5,9673 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2011-10-08 16:16:23Z

1

Assuming key is the primary key of the table:

DELETE FROM the_table t1
WHERE t1.key not in (select min(t2.key)
                     from the_table t2
                     group by lower(t2.field));

answered Oct 8, 2011 at 16:16

user330315

Collectives™ on Stack Overflow

PostgreSQL delete duplicate rows ignoring case

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related