PostgreSQL : How retrieve the same constraint names when reactivation?

In PostgreSQL, I have several foreign key called like fk_test_id.

I need to deactivate all constraints and triggers, do many data transformations, and reactivate constraints and triggers (with the same names before deactivation).

So I did:

1. Create a table temp_constraints
2. Drop constraints
3. Transformation on data
4. To reactivate, I need to keep the same name I had before deactivation.

CREATE TABLE IF NOT EXISTS public.temp_constraints (
    constraintname name COLLATE pg_catalog."C",
    tablename regclass,
    definition text COLLATE pg_catalog."default",
    contype "char"
)

TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.temp_constraints
    OWNER to clip;
    
INSERT INTO public.temp_constraints 
SELECT conname constraintname, conrelid::regclass tablename, pg_get_constraintdef(oid) definition, contype 
FROM pg_catalog.pg_constraint
WHERE conrelid <> '-'::regclass;



DO $$
DECLARE constraint_name TEXT;
DECLARE constraint_table TEXT;
BEGIN
    -- Drop foreign key constraints first
    FOR constraint_name, constraint_table IN
    SELECT constraintname, tablename FROM temp_constraints WHERE contype = 'f'
        LOOP
            EXECUTE 'ALTER TABLE ' || constraint_table || ' DROP CONSTRAINT IF EXISTS "' || constraint_name || '" CASCADE;';
        END LOOP;
        
-- Drop all other constraints
    FOR constraint_name, constraint_table IN
    SELECT constraintname, tablename FROM temp_constraints WHERE contype <> 'f'
        LOOP
            EXECUTE 'ALTER TABLE ' || constraint_table || ' DROP CONSTRAINT IF EXISTS "' || constraint_name || '" CASCADE;';
        END LOOP;
END $$;

DO $$
DECLARE constraint_table TEXT;
DECLARE constraint_definition TEXT;
BEGIN
    IF EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'temp_constraints') THEN
        FOR constraint_table, constraint_definition IN 
        SELECT tablename, definition FROM temp_constraints ORDER BY contype DESC
            LOOP
                EXECUTE 'ALTER TABLE ' || constraint_table || ' ADD ' || constraint_definition || ';';
            END LOOP;
    END IF;
END $$;

Exemple of change:

• Before deactivation: fk_test_id
• After reactivation: test_uuid_fkey

I don't understand why I don't retrieve the names I had. How can I do to keep the same names I have at the beginning ?

The constraint_definition column does not contain the constraint name. You have to set it explicitly with CONSTRAINT constraint_name:

FOR constraint_name, constraint_table, constraint_definition IN 
SELECT constraintname, tablename, definition FROM temp_constraints ORDER BY contype DESC
  LOOP
    EXECUTE 'ALTER TABLE ' || constraint_table || ' ADD CONSTRAINT ' || constraint_name || ' ' || constraint_definition || ';';
  END LOOP;