Add psql command to list constraints

From: Tatsuro Yamada <tatsuro(dot)yamada(dot)tf(at)nttcom(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Add psql command to list constraints
Date: 2021-11-15 01:38:55
Message-ID: 5e63152b-6ba8-647b-284d-dbab502c24d0@nttcom.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 Content-Type Size
test_list_con.sql text/plain 1.9 KB
add_psql_command_to_list_constraint_poc1.patch text/plain 2.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message houzj.fnst@fujitsu.com 2021-11-15 01:50:11 RE: [BUG]Invalidate relcache when setting REPLICA IDENTITY
Previous Message Masahiko Sawada 2021-11-15 01:38:26 Re: Skipping logical replication transactions on subscriber side