How to resolve duplicate constraint names in Postgres?

Question

On Postgres 9.6, I have a table where a foreign key constraint and a unique constraint have the same name (ultimately due to long distinct names being silently truncated to 63 chars). I'm trying to resolve this name conflict through ALTER TABLE RENAME CONSTRAINT, but that yields an error:

table x has multiple constraints named "y"

Which sounds like in order to rename the constraint I first need to… rename the constraint. 🤔

So, how can I do that?

(Side note: the release notes for PostgreSQL 11 have "Fully enforce uniqueness of table and domain constraint names", alluding to this very situation, I suppose, as I discovered while preparing for an upgrade.)

Minimal steps to reproduce:

create table t1 (id uuid primary key);
create table t2 (id uuid primary key);
alter table t1 add constraint oops foreign key (id) references t2 (id);
alter table t1 add constraint oops unique (id);
alter table t1 rename constraint oops to oopsie;

Answer 1

My solution was to drop and recreate the constraints, with one of them given a different name. Postgres will let me drop one constraint by name, but I don't know how it chooses which one or whether it's even deterministic, so the safe route from scripting was to drop both and recreate from there. In the above case, that would go like:

alter table t1 
    drop constraint oops, 
    drop constraint oops,
    add constraint oops foreign key (id) references t2 (id),
    add constraint oops_unique unique (id);

Fortunately, my table was small enough for this to be a practical solution. If it weren't, I'd try updating pg_constraint directly, as Laurenz suggested.