| From: | Tatsuro Yamada <yamatattsu(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Cc: | Álvaro Herrera <alvherre(at)kurilemu(dot)de> |
| Subject: | [PATCH] psql: add \dcs to list all constraints |
| Date: | 2025-10-31 15:19:07 |
| Message-ID: | CAOKkKFsHZpD0V75u1j-6EUrJF=42ShGDOJgyhnp0Z2qsY+ae+Q@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi hackers,
Until PostgreSQL 17, if you wanted to inspect constraints in a database,
you had to either:
- use the "\d" command to check constraints per table, or
- query "pg_constraint" and "pg_attribute" directly.
However, starting from PG18, thanks to Álvaro's work [1], NOT NULL
constraints are now included in "pg_constraint". This means that by
querying "pg_constraint", we can now obtain all kinds of constraints [2][3]
directly.
Building on that improvement, I would like to propose a new psql
meta-command to **list all constraints** in the database.
## Motivation
This command would help DBAs and users to easily understand:
- which tables have constraints
- how many constraints exist in the database overall
- whether all constraints have been properly created after a migration,
etc.
In other words, it would serve as a convenient tool for quickly
validating schema integrity.
Given that psql already has a wide variety of meta-commands — from
frequently used to rarely used ones — I believe adding this one would
not cause any issues.
## Usage examples
-- Show all constraints
\dcs+ con_*
List of constraints
Schema | Name | Definition
| Table
--------+----------------------------+----------------------
-----------------------------------+-------
public | con_c_pkey | PRIMARY KEY (primary_col)
| con_c
public | con_c_primary_col_not_null | NOT NULL primary_col
| con_c
public | con_p_check_col_check | CHECK ((check_col >= 0))
| con_p
public | con_p_exclusion | EXCLUDE USING btree (exclusion_col
WITH =) | con_p
public | con_p_foreign_col_fkey | FOREIGN KEY (foreign_col) REFERENCES
con_c(primary_col) | con_p
public | con_p_notnull_col_not_null | NOT NULL notnull_col
| con_p
public | con_p_pkey | PRIMARY KEY (primary_col)
| con_p
public | con_p_primary_col_not_null | NOT NULL primary_col
| con_p
public | con_p_trigger | TRIGGER
| con_p
public | con_p_unique_col_key | UNIQUE (unique_col)
| con_p
(10 rows)
-- Show only NOT NULL constraints (added "n" for filter)
\dcsn+ con_*
List of constraints
Schema | Name | Definition | Table
--------+----------------------------+----------------------+-------
public | con_c_primary_col_not_null | NOT NULL primary_col | con_c
public | con_p_notnull_col_not_null | NOT NULL notnull_col | con_p
public | con_p_primary_col_not_null | NOT NULL primary_col | con_p
(3 rows)
## About the patch
The patch includes:
- \dcs meta-command
- Tab completion support
- Regression tests
- Documentation
and applies cleanly to the master branch.
## Discussion point: meta-command naming
I'd like to ask for opinions (and votes) on the command name.
Here are some candidates. The one with the most votes will be adopted
as the final name.
\dcs
-> uses the first letters of the two syllables in "con-straint"
\dco
-> short form using the first two letters of "constraint"
\G
-> an idea based on the synonym "guarantee," which semantically fits
the concept
Please find the attached file.
Feedback and suggestions are very welcome.
[1]: Changes to NOT NULL in Postgres 18
https://www.enterprisedb.com/blog/changes-not-null-postgres-18
[2]: 5.5. Constraints
https://www.postgresql.org/docs/current/ddl-constraints.html
[3]: constraint trigger
https://www.postgresql.org/docs/current/sql-createtrigger.html#id-1.9.3.93.6
Thanks,
Tatsuro Yamada
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Add-list-constraints-meta-command-dcs-on-psql.patch | application/octet-stream | 22.7 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jeff Davis | 2025-10-31 15:25:46 | Re: Remaining dependency on setlocale() |
| Previous Message | Tom Lane | 2025-10-31 15:17:11 | Re: abi-compliance-check failure due to recent changes to pg_{clear,restore}_{attribute,relation}_stats() |