Trying to update column on postgresql

I am trying to update my table from another tmp table called fake_email but when I ran the following:

update users
SET email = fake_email2.email
FROM fake_email2
where users.id = fake_email2.id

I got the following error:

ERROR:  duplicate key value violates unique constraint "index_users_on_email"

Is there a way to update it?

Answer

The problem is that your users table has a Constraint on it that requires the email fields to be unique for all rows in the users table.

Run the following query to locate the current rows that match your incoming data set:

SELECT users.id, users.email, fake_email2.email, *
FROM users
INNER JOIN fake_email2 ON users.id = fake_email2.id

In fact, you can find the duplicates with this query:

SELECT users.id, users.email, fake_email2.email
FROM users
INNER JOIN fake_email2 ON users.id = fake_email2.id
WHERE EXISTS (SELECT u2.email FROM users as u2 where u2.Id <> users.Id AND u2.email = fake_email2.email )

So you have two options:

1. Remove the unique constraint
2. Remove the Row that has a duplicate email address

Publishing a list of the data, structures and the create script for the constraint might help with specifics, but at the end of the day, your DBA has specified that the email address is unique for each row, you should not try to circumvent that.