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.

For not null you can check information_schema.columns:https://www.postgresql.org/docs/9.6/infoschema-columns.html. NOT NULL can also be enforced via a CHECK constraint, I beleive you can find that in the information_scjema as well — Lennart - Slava Ukraini, Aug 06 '19 at 19:49
https://www.postgresql.org/docs/9.5/infoschema-table-constraints.html — Lennart - Slava Ukraini, Aug 06 '19 at 19:51

score 0 · Accepted Answer · answered Aug 06 '19 at 17:47

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)

score 0 · Answer 2 · edited May 10 '23 at 11:40

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

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

2 Answers2

Query result as like