Duplicate data despite unique constraint
| От | Jonas Tehler |
|---|---|
| Тема | Duplicate data despite unique constraint |
| Дата | |
| Msg-id | F7646BD2-62E0-4137-A8C7-7D0F22E39760@tehler.se обсуждение исходный текст |
| Ответы |
Re: Duplicate data despite unique constraint
Re: Duplicate data despite unique constraint |
| Список | pgsql-general |
Hi,
We’re running Postgresql 9.4.5 on Amazon RDS. One of our tables looks something like this:
CREATE TABLE users
(
...
email character varying(128) NOT NULL,
...
CONSTRAINT users_email_key UNIQUE (email)
)
Despite this we have rows with very similar email values. I discovered the problem when I tried to add a column and got the following error:
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: could not create unique index "users_email_key"
DETAIL: Key (email)=(xxx@yyy.com) is duplicated.
: ALTER TABLE "users" ADD COLUMN "client_branch" character varying(255) DEFAULT ‘beta'
Now look at this:
=> select email from users where email = 'xxx@yyy.com';
email
---------------------------
(1 row)
=> select email from users where email LIKE 'xxx@yyy.com';
email
---------------------------
(2 rows)
I have tried to compare the binary data in various ways, email::bytes, md5(email), encode(email::bytea, 'hex’), char_length(email) and it all looks the same for both rows.
Any suggestions how I can discover the difference between the values and how they could have been added without triggering the constraint? I know that the values were added after the constraint was added.
The data was added from a Ruby on Rails app that also has unique constraints on the email field and validation on the email format.
/ Jonas
В списке pgsql-general по дате отправления: