How to get the name of a unique constraint in postgresql?

Question

I need to drop a unique constraint from a postgresql table, but I didn't give it a name in the schema. Does anybody know, how to get the name of such a constraint, or how to drop it?

Solution 1

That is something like (for single column constaint):

tableName_columnName_key

To get constaint name write (in psql):

\d tableName

or use pg_constraint system catalog:

SELECT conname
FROM pg_constraint
WHERE conrelid =
    (SELECT oid 
    FROM pg_class
    WHERE relname LIKE 'tableName');

Also you can get it from pgAdmin in objects tree.

Solution 2

SELECT conname
FROM pg_constraint
WHERE conrelid = 'mytable'::regclass::oid