How to list all constraints of a table in PostgreSQL by schema?

Question

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.

Answer 1

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);
CREATE TABLE
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)

Answer 2

You can execute this query

 select  distinct 'Alter table '||c.conrelid::regclass||' Add CONSTRAINT '||c.conname||' '||
       pg_get_constraintdef(c.oid)||';'
       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);