Yes, I'm aware there is a question very similar to mine, the difference is that I want all constraints, including not null, which no answer is able to do.


2 Answers 2


Specifically for the "not null" part, thanks to psql -E to see what happens when you do \d+, and adapted/simplified from it, the following works:

test=# CREATE TABLE nonull (id integer NOT NULL);
test=# \d+ nonull
                        Table "public.nonull"
 Column |  Type   | Modifiers | Storage | Stats target | Description
 id     | integer | not null  | plain   |              |

test=# SELECT a.attname, a.attnotnull FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='nonull') AND a.attnum > 0 and NOT a.attisdropped;
 attname | attnotnull
 id      | t
(1 row)

You can execute this query

 select  distinct 'Alter table '||c.conrelid::regclass||' Add CONSTRAINT '||c.conname||' '||
       FROM pg_constraint c
            INNER JOIN pg_namespace n
                       ON n.oid = c.connamespace
            CROSS JOIN LATERAL unnest(c.conkey) ak(k)
            INNER JOIN pg_attribute a
                       ON a.attrelid = c.conrelid
                          AND a.attnum = ak.k
       WHERE c.conrelid::regclass::text = 'member'
       --WHERE c.conrelid::regclass::text in(select distinct tablename from databasesetting order by tablename asc)

Query result as like

Alter table pacservice 
Add CONSTRAINT pacservice_pkey 
PRIMARY KEY (healthid,pregno, serviceid);

Alter table module_start_date 
Add CONSTRAINT module_start_date_register_type_check 
CHECK ((register_type = ANY (ARRAY['c'::bpchar, 'f'::bpchar])));

Alter table pregwomenfpi 
Add CONSTRAINT pregwomenfpi_pkey 
PRIMARY KEY (healthid, pregno);

Your Answer

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

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