How to generate constraint sql?

Question

I'm looking for a method that can generate the creating sql, like PgAdmin's SQL tab while having selected the constraint. (how pg admin does it?)

I want to implement a generic method that can drop and recreate a foreign key constraint after a process.

Answer 1

Postgresql has pg_get_constraintdef function which is for generating a create statement of specific constraint.

Here is an example;

Schema (PostgreSQL v13)

create table test (
  id integer primary key,
  data integer check (data between 1 and 100)
);


create table test2 (
  id integer primary key,
  data integer,
  test_id integer references test (id)
);

---

Query #1

select
    connamespace::regnamespace "Schema",
    conrelid::regclass "Table",
    conname "Constraint",
    pg_get_constraintdef(oid) "Definition",
    format ('ALTER TABLE %I.%I ADD CONSTRAINT %I %s;', 
                connamespace::regnamespace,
                conrelid::regclass,
                conname,
                pg_get_constraintdef(oid)
           )
    
  from pg_constraint 
  where 
    conname IN (
        'test_data_check', 'test_pkey', 
        'test2_pkey', 'test2_test_id_fkey' 
    );

Schema	Table	Constraint	Definition	format
public	test	test_data_check	CHECK (((data >= 1) AND (data <= 100)))	ALTER TABLE public.test ADD CONSTRAINT test_data_check CHECK (((data >= 1) AND (data <= 100)));
public	test	test_pkey	PRIMARY KEY (id)	ALTER TABLE public.test ADD CONSTRAINT test_pkey PRIMARY KEY (id);
public	test2	test2_pkey	PRIMARY KEY (id)	ALTER TABLE public.test2 ADD CONSTRAINT test2_pkey PRIMARY KEY (id);
public	test2	test2_test_id_fkey	FOREIGN KEY (test_id) REFERENCES test(id)	ALTER TABLE public.test2 ADD CONSTRAINT test2_test_id_fkey FOREIGN KEY (test_id) REFERENCES test(id);

---

View on DB Fiddle