Rename the constraint of a possibly referenced primary key in PostgreSQL
Not sure why Teradata has that limitation, but ALTER INDEX ... RENAME TO ... should be fine in PostgreSQL even when other tables have foreign keys depending on that index. PostgreSQL has fairly sophisticated tracking of such dependencies -- for example, if you tried to do ALTER TABLE foo DROP CONSTRAINT foo_pkey; you would see a complaint like:


  ERROR:  cannot drop constraint foo_pkey on table foo because other objects depend on it
  
  DETAIL: constraint bar_fkey on table bar depends on index foo_pkey
  
  HINT: Use DROP ... CASCADE to drop the dependent objects too.


You can also see in the pg_constraint table (e.g. via SELECT * FROM pg_constraint WHERE conname = 'bar_fkey' for some referencing foreign key bar_fkey) that PostgreSQL is internally tracking this dependency by the OID (in particular, the conindid column in pg_constraint) of the index rather than the name of the index. The OID of the index does not change when you use ALTER INDEX ... RENAME TO ..., which is how Postgres avoids becoming confused by the rename.