How to drop a unique constraint on a column in Postgres?

Question

This is my database table

CREATE TABLE cart (
  id           UUID      NOT NULL PRIMARY KEY,
  shop_user_id UUID UNIQUE
);

And when I try to delete the UNIQUE constraint on shop_user_id I get the sql 42601 error

This is the query I use to delete the unique constraint

ALTER TABLE cart DROP UNIQUE shop_user_id;

Solution 1

To find the name of the unique constraint, run

SELECT conname
FROM pg_constraint
WHERE conrelid = 'cart'::regclass
  AND contype = 'u';

Then drop the constraint as follows:

ALTER TABLE cart DROP CONSTRAINT cart_shop_user_id_key;

Replace cart_shop_user_id_key with whatever you got from the first query.

Solution 2

For example:

    CREATE TABLE teachers( teacher_id SERIAL PRIMARY KEY,
email VARCHAR(250) UNIQUE NOT NULL);


select * from information_schema.table_constraints;

ALTER TABLE teachers DROP CONSTRAINT teachers_email_key;

Comments

[JNevill]

When you get a syntax error, that's usually good time to RTM

[Vinaya Nayak]

Tried. But can't. Because shop_user_id is the column name and not constraint name

[Kveld Ulf]

select * from information_schema.table_constraints; Find the constraint. Make sure to drop it from the table, then drop the unique value.

[Vinaya Nayak]

I ran select * from information_schema.table_constraints; This still shows the constraint name in there. But the problem is how to drop it?

[thesecretmaster]

@GhostCat Only moderators can delete comments, so the best way to get comments cleaned up is to flag them.

[GhostCat]

You can always delete your own comments. And when two people have a conversation, do you really think you need a moderator to get that cleaned up? Seriously: these people have a ton of requests coming in daily. Don't waste their time on something that can be resolved by everyone just getting his own things sorted out.

[niedomnie]

Does not work always: select * from pg_indexes where indexname = 'train_on_track_history_unique'; returns index train_on_track_history_unique for table train_on_track_history, schema edr # alter table edr.train_on_track_history drop constraint train_on_track_history_unique; constraint "train_on_track_history_unique" of relation "train_on_track_history" does not exist #drop index train_on_track_history_unique; does not exist

[ScrappyDev]

I like this answer as an addendum to the accepted answer, since the table_constraints view provides a much more human readable result.