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

Hi Tatsuro

On 31/10/2025 16:19, Tatsuro Yamada wrote:
> Please find the attached file.
> Feedback and suggestions are very welcome.

Thanks for the patch!

I've been playing with a few edge cases and everything seems to work
just fine. Bellow I am listing the cases I tested, so that you can take
a look if some of them should be included in the regression tests - not
sure if it is necessary, since this feature is only reading the
constraint definitions from the catalog.


CREATE TABLE zoo (
  cage int,
  animal text,
  is_aggressive boolean,
  CONSTRAINT no_different_animals_in_same_cage
    EXCLUDE USING gist (
      cage WITH =,
      animal WITH <>,
      int4(is_aggressive) WITH <>
    )
);
CREATE TABLE

postgres=# \dcs+
                                                        List of constraints
 Schema |               Name                |
      Definition                                   | Table

--------+-----------------------------------+-------------------------------------------------------------------------------+-------
 public | no_different_animals_in_same_cage | EXCLUDE USING gist (cage
WITH =, animal WITH <>, int4(is_aggressive) WITH <>) | zoo
(1 row)


postgres=# CREATE TABLE zoo_partial (
  cage int,
  animal text,
  is_aggressive boolean,
  CONSTRAINT zoo_partial_excl
    EXCLUDE USING gist (
      cage WITH =,
      animal WITH <>
    )
    WHERE (is_aggressive)
);
CREATE TABLE
postgres=# \dcs+ zoo_partial*
                                               List of constraints
 Schema |       Name       |                               Definition
                           |    Table
--------+------------------+------------------------------------------------------------------------+-------------
 public | zoo_partial_excl | EXCLUDE USING gist (cage WITH =, animal
WITH <>) WHERE (is_aggressive) | zoo_partial
(1 row)


postgres=# CREATE TABLE zoo_deferrable (
  cage int,
  animal text,
  CONSTRAINT zoo_deferrable_excl
    EXCLUDE USING gist (
      cage WITH =,
      animal WITH <>
    )
    DEFERRABLE INITIALLY DEFERRED
);
CREATE TABLE
postgres=# \dcs+ zoo_deferrable_excl
                                                      List of constraints
 Schema |        Name         |
Definition                                   |     Table

--------+---------------------+--------------------------------------------------------------------------------+----------------
 public | zoo_deferrable_excl | EXCLUDE USING gist (cage WITH =, animal
WITH <>) DEFERRABLE INITIALLY DEFERRED | zoo_deferrable
(1 row)


postgres=# CREATE TABLE zoo_expr (
  cage int,
  animal text,
  is_aggressive boolean,
  CONSTRAINT zoo_expr_excl
    EXCLUDE USING gist (
      cage WITH =,
      lower(animal) WITH =,
      int4(is_aggressive) WITH <>
    )
);
CREATE TABLE
postgres=# \dcs+ zoo_expr_excl
                                                   List of constraints
 Schema |     Name      |                                     Definition
                                     |  Table

--------+---------------+-------------------------------------------------------------------------------------+----------
 public | zoo_expr_excl | EXCLUDE USING gist (cage WITH =, lower(animal)
WITH =, int4(is_aggressive) WITH <>) | zoo_expr
(1 row)


postgres=# CREATE TABLE zoo_check (
  cage int,
  animal text,
  is_aggressive boolean
);

ALTER TABLE zoo_check
  ADD CONSTRAINT zoo_check_aggr
  CHECK (is_aggressive IS NOT NULL)
  NOT VALID;
CREATE TABLE
ALTER TABLE
postgres=# \dcs+ zoo_check_aggr
                                 List of constraints
 Schema |      Name      |                  Definition
|   Table
--------+----------------+-----------------------------------------------+-----------
 public | zoo_check_aggr | CHECK ((is_aggressive IS NOT NULL)) NOT VALID
| zoo_check
(1 row)


postgres=# CREATE TABLE zoo_parent (
  cage int PRIMARY KEY
);

CREATE TABLE zoo_child (
  animal text
) INHERITS (zoo_parent);
CREATE TABLE
CREATE TABLE
postgres=# \dcs+ zoo_parent_cage_not_null
                      List of constraints
 Schema |           Name           |  Definition   |   Table
--------+--------------------------+---------------+------------
 public | zoo_parent_cage_not_null | NOT NULL cage | zoo_parent
 public | zoo_parent_cage_not_null | NOT NULL cage | zoo_child
(2 rows)


CREATE TABLE zoo_part (
  cage int,
  animal text,
  CONSTRAINT zoo_part_pk PRIMARY KEY (cage)
) PARTITION BY RANGE (cage);
CREATE TABLE
db=# CREATE TABLE zoo_part_1
  PARTITION OF zoo_part
  FOR VALUES FROM (0) TO (100);
CREATE TABLE
db=# \dcs+
                        List of constraints
 Schema |          Name          |     Definition     |   Table
--------+------------------------+--------------------+------------
 public | zoo_part_1_pkey        | PRIMARY KEY (cage) | zoo_part_1
 public | zoo_part_cage_not_null | NOT NULL cage      | zoo_part
 public | zoo_part_cage_not_null | NOT NULL cage      | zoo_part_1
 public | zoo_part_pk            | PRIMARY KEY (cage) | zoo_part
(4 rows)


postgres=# drop table zoo_drop ;
DROP TABLE
postgres=# CREATE TABLE zoo_drop (
  cage int,
  animal text,
  CONSTRAINT zoo_drop_uq UNIQUE (cage, animal)
);
CREATE TABLE
postgres=# \dcs+
                   List of constraints
 Schema |    Name     |      Definition       |  Table
--------+-------------+-----------------------+----------
 public | zoo_drop_uq | UNIQUE (cage, animal) | zoo_drop
(1 row)

postgres=# ALTER TABLE zoo_drop DROP COLUMN animal;
ALTER TABLE
postgres=# \dcs+
        List of constraints
 Schema | Name | Definition | Table
--------+------+------------+-------
(0 rows)


One nitpick: the order of the constraints is different from the one in \d+:

postgres=# CREATE TABLE t (
  c text NOT NULL,
  b text NOT NULL,
  a text NOT NULL
);
CREATE TABLE
postgres=# \d+ t
                                           Table "public.t"
 Column | Type | Collation | Nullable | Default | Storage  | Compression
| Stats target | Description
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
 c      | text |           | not null |         | extended |
|              |
 b      | text |           | not null |         | extended |
|              |
 a      | text |           | not null |         | extended |
|              |
Not-null constraints:
    "t_c_not_null" NOT NULL "c"
    "t_b_not_null" NOT NULL "b"
    "t_a_not_null" NOT NULL "a"
Access method: heap

postgres=# \dcs+ t*
            List of constraints
 Schema |     Name     | Definition | Table
--------+--------------+------------+-------
 public | t_a_not_null | NOT NULL a | t
 public | t_b_not_null | NOT NULL b | t
 public | t_c_not_null | NOT NULL c | t
(3 rows)

For consistency, it would be nice to have both options listing in the
same order, but in case it would mean adding too much complexity to the
code, I'd say it is just fine as-is.


Thanks!

Best, Jim