drop primary key constraint in postgresql by knowing schema and table name only

Question

As far I know the only way of dropping primary key in postgresql is:

ALTER TABLE schema.tableName DROP CONSTRAINT constraint_name;

the constraint name by default is tableName_pkey. However sometimes if table is already renamed I can’t get the original table name to construct right constraint name.

For example, for a table created as A then renamed to B the constraint remains A_pkey but I only have the table name B.

Do you know right way to drop the pkey constraint by knowing only the schema name and table name ?

I am writing program for doing this so I need to use only SQL queries. Solutions like "open pgAdmin and see the constraint name" will not work.

Answer 1

You can use information from the catalog tables like so:

Create a table with id as the primary key

create table test1 (id int primary key, name text);

Create the SQL to drop the key

select concat('alter table public.test1 drop constraint ', constraint_name) as my_query
from information_schema.table_constraints
where table_schema = 'public'
      and table_name = 'test1'
      and constraint_type = 'PRIMARY KEY';

The result will be:

alter table public.test1 drop constraint test1_pkey

You can create a stored function to extract this query and then execute it.

Answer 2

login to the database using psql, the command line tool.

Then type:

\d <table_name>

for example:

\d claim
                                                  Table "public.claim"
             Column             |            Type             | Collation | Nullable |              Default              
--------------------------------+-----------------------------+-----------+----------+-----------------------------------
 id                             | integer                     |           | not null | nextval('claim_id_seq'::regclass)
 policy_id                      | integer                     |           |          | 
 person_id                      | integer                     |           |          | 
 incident_id                    | integer                     |           |          | 
 first_notification_of_loss     | timestamp without time zone |           |          | 
 police_reference               | character varying(40)       |           |          | 
 photos_to_follow               | boolean                     |           |          | 
 sketch_to_follow               | boolean                     |           |          | 
 description_of_weather         | character varying(2000)     |           |          | 
 description_of_property_damage | character varying(2000)     |           |          | 
 created_at                     | timestamp without time zone |           | not null | now()
 updated_at                     | timestamp without time zone |           | not null | 
Indexes:
    "primary_key_claim" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "foreign_key_claim_incident" FOREIGN KEY (incident_id) REFERENCES incident(id)
    "foreign_key_claim_person" FOREIGN KEY (person_id) REFERENCES person(id)
    "foreign_key_claim_policy" FOREIGN KEY (policy_id) REFERENCES policy(id)
Referenced by:
    TABLE "claimant" CONSTRAINT "foreign_key_claimant_claim" FOREIGN KEY (claim_id) REFERENCES claim(id)
    TABLE "damage" CONSTRAINT "foreign_key_damage_claim" FOREIGN KEY (claim_id) REFERENCES claim(id)
    TABLE "witness" CONSTRAINT "foreign_key_witness_claim" FOREIGN KEY (claim_id) REFERENCES claim(id)

This shows you the primary key name (as well as other stuff).

If you want to do this programmatically and you are using Java or another language that uses the JDBC interface, you can use the class DatabaseMetaData, method getPrimaryKeys.

Otherwise, the other answer, selecting from the system catalogs, is the way to go.