Hi,
I have been wondering why there is no meta-command for listing
constraints in psql. So, I created a POC patch by using my
experience developing \dX command in PG14.
This feature is helpful for DBAs when they want to check or
modify the definition of constraints.
The current status of the POC patch is as follows:
- Add "\dco" command to list constraints from pg_constraint
- Not implemented yet:
- NOT NULL constraint, and so on (based on pg_attribute)
- Tab completion
- Regression test
- Document
The following is test results (See attached test_list_con.sql)
====================================================================
postgres=# \dco
List of constsraints
Schema | Name | Definition | Table
--------+-------------------------+---------------------------------------------------------+----------
public | t01_chk_price_check | CHECK ((price > (0)::numeric)) | t01_chk
public | t02_uniq_product_no_key | UNIQUE (product_no) | t02_uniq
public | t03_pk1_pkey | PRIMARY KEY (product_no) | t03_pk1
public | t03_pk2_product_no_key | UNIQUE (product_no) | t03_pk2
public | t04_fk_pkey | PRIMARY KEY (order_id) | t04_fk
public | t04_fk_product_no_fkey | FOREIGN KEY (product_no) REFERENCES t03_pk1(product_no) | t04_fk
public | t05_ex_c_excl | EXCLUDE USING gist (c WITH &&) | t05_ex
(7 rows)
====================================================================
I have the following two questions that need to be discussed.
Questions:
(1) What strings should be assigned as meta-command for this feature?
Currently, \dc and \dC are not available, so I tentatively
assigned \dco. However, I do not have a strong opinion, so please
let me know if you have any suggestions.
(2) About domain constraints
There is the \dD command to show a list of domain constraints.
So I think this feature should not include it. Is it Okay?
If I can get "+1" for this new feature development, I would like to
improve the patch by adding NOT NULL constraints, and so on.
Any advice or comments would be appreciated.
Thanks,
Tatsuro Yamada