PostgreSQL drop constraint with unknown name

Question

I have an SQL script that needs to drop several constraints and restore them at the end, but the constraint names are auto-generated and will be different each time the script is run.

I know how to get the constraint name from the table names, but it doesn't seem possible to use this information in the drop statement.

select conname from pg_constraint where
   conrelid = (select oid from pg_class where relname='table name')
   and confrelid = (select oid from pg_class where relname='reference table');

alter table something drop constraint (some subquery) is a syntax error.

Ideally I would like to get the constraint name and store it in a variable, but it doesn't seem that Postgres supports that and I can't make it work with psql \set.

Is this even possible?

You would *at least* need dynamic SQL to do this. Object names (tablenames,columnnames,etc) can Never be specified as variables (or subqueries) without first constructing the query (in a string) and then executing that. Using sed/awk to generate the "DROP xxx" lines in a file, and piping that file through psql could be a workaround. It will still be hard to maintain some "atomicity" (But DDL's are always difficult in that respect) — wildplasser, Sep 12 '12 at 20:13
@wildplasser okay, I thought maybe psql would have some functionality to do that. Otherwise, you can just put your comment as answer. — takteek, Sep 12 '12 at 20:41
I am not that handy with dynamic query building (I actually *hate* it) Others will probably fill it in. — wildplasser, Sep 12 '12 at 20:49

Erwin Brandstetter · Accepted Answer · 2012-09-12T22:20:37.213

To dynamically drop & recreate a foreign key constraint, you could wrap it all in a function or use the DO command:

DO
$body$
DECLARE
   _con text := (
      SELECT quote_ident(conname)
      FROM   pg_constraint
      WHERE  conrelid = 'myschema.mytable'::regclass
      AND    confrelid = 'myschema.myreftable'::regclass
      LIMIT 1 -- there could be multiple fk constraints. Deal with it ...
      );

BEGIN
   EXECUTE '
      ALTER TABLE wuchtel12.bet DROP CONSTRAINT ' || _con;

   -- do stuff here

   EXECUTE '
      ALTER TABLE myschema.mytable
      ADD CONSTRAINT ' || _con || ' FOREIGN KEY (col)
      REFERENCES myschema.myreftable (col)';
END
$body$

You must own the table to use ALTER TABLE.
Else you can create a function with LANGUAGE plpgsql SECURITY DEFINER (using the same body) and

ALTER FUNCTION foo() OWNER TO postgres;

postgres being a superuser here - or the owner of the table.
But be sure to know what the manual has to say about security.

The manual also has more on dynamic commands.

score 0 · Answer 2 · answered Dec 14 '21 at 10:21

You can use stored procedure also.

CREATE OR REPLACE PROCEDURE public.p_costraint()
 LANGUAGE plpgsql
AS $procedure$
DECLARE _constrint text;
begin
-- for dynamic change the constraint. 
    _constrint := (
      SELECT quote_ident(conname)
      FROM   pg_constraint
        WHERE  conrelid = 'test.contacts'::regclass
        AND    confrelid = 'test.customers'::regclass
      LIMIT 1 -- there could be multiple fk constraints. Deal with it ...
      );
    _constrint := _constrint || 'test';
    EXECUTE '
      ALTER TABLE test.contacts
      ADD CONSTRAINT ' || _constrint || ' FOREIGN KEY (customer_id)
      REFERENCES test.customers (customer_id)';
    RAISE NOTICE 'hello, world!';
end
$procedure$;

In here. constraint name is used as a text variable.
You can just call it: call public.p_costraint();
It will return :

NOTICE:  hello, world!
CALL

PostgreSQL drop constraint with unknown name

2 Answers2

Linked