postgresql - Postgres小写列并删除重复项

Question

我有下表：

Customers
---------
name          text
object_id     integer
created_time  timestamp with time zone 

Indexes:
    "my_index" UNIQUE CONSTRAINT, btree (name, object_id, created_time)

唯一索引工作正常，但后来我得到了重复的数据，例如：

Name  |  object_id  |  created_time
------------------------------------
john  | 1           | 2018-02-28 15:42:14.30573+00
JOHN  | 1           | 2018-02-28 15:42:14.30573+00

因此，我尝试将 name 列中的所有数据小写：

UPDATE customers SET name=lower(name) WHERE name != LOWER(name);

但是这个过程产生了一个错误，因为现在我将违反索引：

ERROR:  duplicate key value violates unique constraint "my_index"
DETAIL:  Key (name, object_id, created_time)=(john, 1, 2018-02-28 15:42:14.30573+00) already exists.

我可以使用什么样的程序来删除转换为小写后生成索引冲突的行？

标签： postgresqlindexinglowercase

Answer 1

如果你有'JOHN'并且'John'在你的桌子上但没有'john'它会变得混乱。这是一个解决方案。

insert into customers
  select distinct lower("name") ,object_id,created_time from customers
    where name <> lower(name)
      and not (lower("name") ,object_id,created_time)
       in (select * from customers);

delete from customers where name <> lower(name);

之后考虑：

alter table customers alter column name type citext;