CODEWITHSUNDEEP
postgresqlprimary-keyalter-table
Knight
Knight

Reputation: 573

Postgres retrieve name of a constraint

How can I check the name of my constraint?

For instance:

ALTER TABLE contractor_contractor ADD CONSTRAINT commerce_contractor_pkey  PRIMARY KEY(id);

When I try to describe my table with \d I don't see any info about my primary key.

Upvotes: 1

Views: 113

Answers (1)

Mureinik
Mureinik

Reputation: 311326

You could query the constraint name from the information schema:

SELECT constraint_name
FROM   information_schema.table_constraints
WHERE  table_catalog = 'my_catalog_name' AND    -- Database name
       table_schema = 'my_schema_name' AND      -- Often "public"
       table_name = 'contractor_contractor' AND 
       constraint_type = 'PRIMARY KEY';

Also note that newer version of plsql do provide information on primary keys under the indexes section. E.g.:

db=> CREATE TABLE contractor_contractor (id INT);
CREATE TABLE
db=> ALTER TABLE contractor_contractor
db-> ADD CONSTRAINT commerce_contractor_pkey  PRIMARY KEY(id);
ALTER TABLE
db=> \d contractor_contractor 
Table "public.contractor_contractor"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
Indexes:
    "commerce_contractor_pkey" PRIMARY KEY, btree (id)

Upvotes: 1

Related Questions