Add psql command to list constraints - Mailing list pgsql-hackers

From Tatsuro Yamada
Subject Add psql command to list constraints
Date
Msg-id 5e63152b-6ba8-647b-284d-dbab502c24d0@nttcom.co.jp
Whole thread Raw
Responses Re: Add psql command to list constraints
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Masahiko Sawada
Date:
Subject: Re: Skipping logical replication transactions on subscriber side
Next
From: "houzj.fnst@fujitsu.com"
Date:
Subject: RE: [BUG]Invalidate relcache when setting REPLICA IDENTITY