Constraint name update in PostgreSQL

Question

To rename an existing constraint in PostgreSQL 9.2 or newer, you can use ALTER TABLE:

ALTER TABLE name RENAME CONSTRAINT constraint_name TO new_constraint_name;

For the primary key, you should be able to just:

ALTER INDEX commerce_contractor_pkey RENAME TO whatever_new_name

That won't work for other types of constraints though. The best option there is to drop the old one and create a new one. Be sure to do it inside a transaction, so the system isn't live without it during rebuild. (And if you can't do it in a transaction, be sure to create the new one first, before dropping the old one)

We found that primary keys often lag behind the main table name. This script helped us identify and fix the ones with issues.

select 
    table_name, 
    constraint_name , 
    'ALTER TABLE ' || table_name || ' RENAME CONSTRAINT ' || constraint_name || ' TO ' || left(table_name, 58) || '_pkey;'
from information_schema.table_constraints tc
where constraint_type  = 'PRIMARY KEY' 
and constraint_name <> left(table_name, 58) || '_pkey';

This finds all the tables where the primary key name is no longer the "default" pattern (<tablename>_pkey) and creates a rename script for each.

The 58 character limit above in code above is to account for the maximum size of constraint names (63bytes).

Obviously sense check what is returned prior to running it. Hope that is helpful for others.