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

Question

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.

Leave a Comment Cancel reply