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

sql postgresql constraints unique-constraint
Pupkov-Zadnij picture Pupkov-Zadnij · Jul 27, 2011 · Viewed 30k times · Source

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?

Answer

Grzegorz Szpetkowski picture Grzegorz Szpetkowski · Jul 27, 2011

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.

Related questions

Postgres: Add constraint if it doesn't already exist

Does Postgres have any way to say ALTER TABLE foo ADD CONSTRAINT bar ... which will just ignore the command if the constraint already exists, so that it doesn't raise an error?

Read More
How to drop all NOT NULL constraints from a PostgreSQL table in one go

Is it possible to drop all NOT NULL constraints from a table in one go? I have a big table with a lot of NOT NULL constraints and I'm searching for a solution that is faster than dropping them separately.

Read More
Adding named foreign key constraints in a SQL Create statement

I currently have: CREATE TABLE galleries_gallery ( id INT NOT NULL PRIMARY KEY IDENTITY, title NVARCHAR(50) UNIQUE NOT NULL, description VARCHAR(256), templateID INT NOT NULL REFERENCES galleries_templates(id), jsAltImgID INT NOT NULL REFERENCES libraryImage(id) jsAltText NVARCHAR(500), dateCreated SMALLDATETIME …

Read More