Trouble Dropping Unique Constraint

Question

In PG:

I made a user table that includes unique emails, but later decided that emails should not be unique. I pushed changes to make my email field non-unique (I use an ORM, so I don't actually have the exact SQL that took place), but PG still won't let me use duplicate email addresses.

I checked the index and it's not unique, but there's a constraint keeping me from having duplicate email addresses. However I'm having trouble dropping this constraint. What am I doing wrong?

SQL> ALTER TABLE "users" DROP CONSTRAINT "unique_users_email"
PGError: ERROR:  constraint "unique_users_email" of relation "users" does not exist

SQL> UPDATE users SET email = '[email protected]'
PGError: ERROR:  duplicate key value violates unique constraint "unique_users_email"
DETAIL:  Key (email)=([email protected]) already exists.

Answer 1

I bet that "unique_users_email" is actually the name of a unique index rather than a constraint. Try:

DROP INDEX "unique_users_email";

Recent versions of psql should tell you the difference between a unique index and a unique constraint when looking at the \d description of a table.