[PATCH] psql: add \dcs to list all constraints

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

Browse pgsql-hackers by date

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