[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
Вложения

В списке pgsql-hackers по дате отправления: