How to retrieve a constraint name in an existing database

In a migration, I want to remove an index from a table (before adding a new one). I can do that with a RunSQL like this:

ALTER TABLE model DROP CONSTRAINT model_pkey

But it turns out that the name of the constraint (for the primary key) is not always the same, because it used to be named differently in the past.

So I'm looking for a way to retrieve the name of constraint, even if it's not the default one.

Here is what I came up with :

DO $$
BEGIN
EXECUTE 'ALTER TABLE model DROP CONSTRAINT ' || (
    SELECT indexname 
    FROM pg_indexes 
    WHERE tablename = 'model' AND indexname LIKE '%pkey'
);
END $$;
Back to Top