drop primary key constraint in postgresql by knowing schema and table name only
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_queryfrom information_schema.table_constraintswhere 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.
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.