PostgreSQL 删除重复行忽略大小写

发布于 2024-12-08 13:51:56 字数 328 浏览 0 评论 0原文

我需要从表中删除在指定字段上具有相同值的行，忽略大小写。例如，如果我有一行将“foo”作为字段值，另一行将“Foo”作为同一字段的值，我只想删除其中一行（保留 1 行）。
我尝试过类似的操作：

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

但这也会删除另一行。
有什么建议吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

彼岸花ソ最美的依靠 2024-12-15 13:51:56

只需将 <> 更改为 <：

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

这样您就可以保留具有最高 key 的行。您还可以使用 > 来保留具有最低 key 的记录。

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.

回复收藏 0 原文

似狗非友 2024-12-15 13:51:56

假设 key 是表的主键：

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

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));

回复收藏 0 原文

~没有更多了~