I'm trying to insert data from one PostgreSQL 9.3 table to another while removing duplicate entries.
I have two tables containing email addresses.
The main table contains emails and a tag for each email address. The combination
(email, tag) must be unique, and for this there is a constraint
Unique(email, tag) on it.
The second table get created on the fly from text files which contain only the email address. There are many duplicates there.
I need to import the data from the temp table into the main one without breaking the constraint above. For a certain file containing email addresses the tag is constant.
CREATE TABLE emails ( email character varying(128), tag bigint, CONSTRAINT "unique-tag-email" UNIQUE (email, tag) )
CREATE TABLE emails_temp (email character varying(128)
Here is my query:
insert into emails(tag,email) select 655,t.email from emails_temp as t where not exists ( select email from emails where email = t.email )
Note: 655 is just a tag of a certain group of email addresses.
This is the error I get:
ERROR: duplicate key value violates unique constraint "unique-tag-email" SQL state: 23505 Detail: Key (email, tag)=(email@example.com, 655) already exists.
There are indeed, two email addresses firstname.lastname@example.org in the file.
Useless to say, because of this error, there is nothing added in the main table (email).
What am I doing wrong?
email@example.com, 655already exists, the constraint is violated and therefore the DML won't succeed
insert ... select distinct 655, t.email ...