Drop all of a table's constraints in Postgres

Question

I need to programmatically drop all of a table's constraints, without knowing all constraint names, and I have so far come up with this bit of code, where 'book' is the table of which I want to drop all constraints.

DO $$
DECLARE i RECORD;
BEGIN
FOR i IN (SELECT conname
    FROM pg_catalog.pg_constraint con
        INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
        INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
        WHERE rel.relname = 'book') LOOP
    EXECUTE format('ALTER TABLE book DROP CONSTRAINT %I', i);
END LOOP;
END $$;

It seems, however, that this not only quotes the contraint names (e.g. "book_pkey") but also adds parantheses ("(book_pkey)"). Or this could be a problem with iterating the constraint names, though executing the SELECT query by itself gives a list of all constraints without parantheses or quotes:

  conname
-----------
 book_pkey
(1 row)

When executing the whole code block, Postgres gives the following error:

ERROR:  constraint "(book_pkey)" of relation "book" does not exist
CONTEXT:  SQL statement "ALTER TABLE book DROP CONSTRAINT "(book_pkey)""
PL/pgSQL function inline_code_block line 17 at EXECUTE

What can I do about this? Manually removing the parantheses can't be the right answer, can it?

Answer 1

This is partly due to the use of %I in the execute statement, when you use %I in the format function, PostgreSQL automatically adds double quotes around the identifier to ensure that it's treated as a single, case-sensitive entity. By using %s instead of %I in the format function, you're essentially telling PostgreSQL not to add the double quotes.

DO $$
DECLARE i RECORD;
BEGIN
FOR i IN (SELECT conname
    FROM pg_catalog.pg_constraint con
        INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
        INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
        WHERE rel.relname = 'book') LOOP
    EXECUTE format('ALTER TABLE book DROP CONSTRAINT %s', i.conname);
END LOOP;
END $$;

This should work.