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.

Table structures:

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) 
 emails_temp as t 
 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 protected], 655) already exists.

There are indeed, two email addresses [email protected] in the file.

Useless to say, because of this error, there is nothing added in the main table (email).

What am I doing wrong?

  • You are not doing anything wrong. You are getting what you coded. 1st There is a unique key. 2nd The where condition in the correlated query select results that violate the unique key....Since [email protected], 655 already exists, the constraint is violated and therefore the DML won't succeed
    – Jason Krs
    Jan 9, 2017 at 21:58
  • 2
    well, to insert distinct values you may want to try selecting distinct values: insert ... select distinct 655, t.email ...
    – mustaccio
    Jan 9, 2017 at 22:18
  • Thanks for the comments.However, I was under the assumption that the clause where not exists ( select email from emails where email = t.email ) is there exactly to prevent email addresses to be duplicated. Select distinct doesn't work either. I get the same error as before.
    – Sorin
    Jan 10, 2017 at 9:14

1 Answer 1


There are 3 possible kinds of duplicates:

  1. Duplicates within the rows of the bulk insert.

  2. Duplicates between inserted rows and existing rows.

  3. Duplicates between inserted rows and concurrently inserted / updated rows from other transactions.

Just like I explained in this closely related answer:

But things have become easier for 2. and 3. since Postgres 9.5 introduced UPSERT (INSERT .. ON CONFLICT DO NOTHING).

INSERT INTO emails(tag,email)
FROM   emails_temp

If your duplicates only stem from duplicate entries in the source (1.), like you indicated, then all you need is DISTINCT. Works in any version of Postgres:

INSERT INTO emails(tag,email)
FROM   emails_temp;
  • Erwin, thanks for the idea. I read about it too, but I can't upgrade to PSQL 9.5. I am stuck with 9.3 for some other reasons which I can't control.
    – Sorin
    Jan 10, 2017 at 9:21
  • @Sorin: Seems like all you need is DISTINCT, really. I added a bit above. Jan 10, 2017 at 14:09
  • 1
    For previous versions without ON CONFLICT: INSERT INTO emails (tag,email) SELECT DISTINCT 655, email FROM emails_temp EXCEPT SELECT tag, email FROM emails WHERE tag = 655 ; Jan 10, 2017 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.