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@xxxxxxx) 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@xxxxxxx'; email --------------------------- (1 row) => select email from users where email LIKE 'xxx@xxxxxxx'; 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 |
