0

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.

1
  • Don't understand why downvoted...
    – cdalxndr
    Aug 14, 2021 at 17:48

1 Answer 1

5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.