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

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

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.

Solution

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.