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)=(x...@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 = 'x...@yyy.com';
email
---------------------------
x...@yyy.com
(1 row)
=> select email from users where email LIKE 'x...@yyy.com';
email
---------------------------
x...@yyy.com
x...@yyy.com
(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
