[PATCH] psql: add \dcs to list all constraints
| От | Tatsuro Yamada | 
|---|---|
| Тема | [PATCH] psql: add \dcs to list all constraints | 
| Дата | |
| Msg-id | CAOKkKFsHZpD0V75u1j-6EUrJF=42ShGDOJgyhnp0Z2qsY+ae+Q@mail.gmail.com обсуждение исходный текст  | 
		
| Список | 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
		
	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
Вложения
В списке pgsql-hackers по дате отправления: