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  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Duplicate data despite unique constraint  (Albe Laurenz <laurenz.albe@wien.gv.at>)
Список 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 по дате отправления:

Предыдущее
От: "dandl"
Дата:
Сообщение: What limits Postgres performance when the whole database lives in cache?
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: Duplicate data despite unique constraint