Skip to content
Advertisement

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:

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.

Advertisement

Answer

You can use information from the catalog tables like so:

Create a table with id as the primary key

Create the SQL to drop the key

The result will be:

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

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement