Postgres Pro
Downloads
Home   >   mailing lists

Duplicate data despite unique constraint - Mailing list pgsql-general

From Jonas Tehler
Subject Duplicate data despite unique constraint
Date
Msg-id F7646BD2-62E0-4137-A8C7-7D0F22E39760@tehler.se
Whole thread Raw
Responses Re: Duplicate data despite unique constraint
Re: Duplicate data despite unique constraint
List pgsql-general
Tree view

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 by date:

Previous
From: "dandl"
Date:
Subject: What limits Postgres performance when the whole database lives in cache?
Next
From: Adrian Klaver
Date:
Subject: Re: Duplicate data despite unique constraint