PostgreSQL 唯一索引 没有数据却提示值已存在？

Question

## 索引
+------------+--------------------------+-----------------------------------------------------+
| Column     | Type                     | Modifiers                                           |
|------------+--------------------------+-----------------------------------------------------|
| id         | bigint                   |  not null default nextval('users_id_seq'::regclass) |
| name       | character varying(50)    |  not null                                           |
| email      | character varying(200)   |  not null                                           |
| mobile     | character varying(20)    |                                                     |
| created_at | timestamp with time zone |  not null default now()                             |
| password   | character varying        |  not null                                           |
| _2fa       | character varying(32)    |                                                     |
+------------+--------------------------+-----------------------------------------------------+
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "idx_users_email" UNIQUE, btree (lower('email'::text))
    "idx_users_name" UNIQUE, btree (lower('name'::text))

## 现有数据
+------+--------+-----------------------+----------+-------------------------------+--------------------------------------------------------------+--------+
| id   | name   | email                 | mobile   | created_at                    | password                                                     | _2fa   |
|------+--------+-----------------------+----------+-------------------------------+--------------------------------------------------------------+--------|
| 2    | test1  | [e-mail] | <null>   | 2018-07-24 21:07:10.387633+08 | $2b$10$BdgYhkiJbEihnOYqpjPrxOO06HOJgKOsLI.n9TS0VQpL4..GT5R6. | <null> |
+------+--------+-----------------------+----------+-------------------------------+--------------------------------------------------------------+--------+

-- 执行sql
INSERT INTO users (name, email, password, _2fa, created_at) VALUES ('test', '[e-mail]', '$2b$10$Vj9tmaebstoLovqlgQ/a9ucM/RJjSoSVG2dYbk.LqxIn6AC/e37oa', Null, '2018-07-24 13:44:26+00:00') RETURNING users.id

得到报错：

ERROR: duplicate key value violates unique constraint "idx_users_email"
DETAIL: Key (lower('email'::text))=(email) already exists.

Answer 1

lower('email'::text) 你每一次插入，索引都是'email'重复
--我猜你是这么写的 create unique index idx_users_email on users USING btree(lower('email'::text))
--改成
create unique index idx_users_email on users USING btree(lower(email))