Обсуждение: [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

On 12/01/2026 06:39, Tatsuro Yamada wrote:
> Comments and suggestions are welcome.


Here a few comments to v2:

== listConstraints() ==

...

if (!showSystem && !pattern)
    appendPQExpBufferStr(&buf,
         "WHERE n.nspname <> 'pg_catalog' \n"
         "  AND n.nspname <> 'information_schema' \n");

if (!validateSQLNamePattern(&buf, pattern,
        !showSystem && !pattern, false,
        "n.nspname", "cst.conname", NULL,
        "pg_catalog.pg_table_is_visible(cst.conrelid)",
        NULL, 3))
{
    termPQExpBuffer(&buf);
    return false;
}

if (!showAllkinds)
{
    appendPQExpBufferStr(&buf, "  AND cst.contype in ("); <== here!
    
....

It looks like that a WHERE condition can be potentially added to the "if
(!showAllkinds)" block even if there is no WHERE clause at all. I'm not
sure if this path is even possible, but perhaps a more defensive
approach here wouldn't be a bad idea, e.g.

...
bool have_where = false;

if (!showSystem && !pattern)
{
    appendPQExpBufferStr(&buf,
                     "WHERE n.nspname <> 'pg_catalog' \n"
                     "  AND n.nspname <> 'information_schema' \n");
    have_where = true;
}

if (!validateSQLNamePattern(&buf, pattern,
                have_where, false,
                "n.nspname", "cst.conname", NULL,
                "pg_catalog.pg_table_is_visible(cst.conrelid)",
                &have_where, 3))     
{

if (!showAllkinds)
{
    appendPQExpBuffer(&buf, " %s cst.contype in (",
                  have_where ? "AND" : "WHERE");
...


What do you think?

== Patch name ==

It'd be better if you format your patch name with the version upfront, e.g.

$ git format-patch -1 -v3



I've tried a few more edge cases and so far everything is working as
expected

postgres=# \set ECHO_HIDDEN on

postgres=# CREATE TABLE zoo (id int PRIMARY KEY, name text);
ALTER TABLE zoo ADD CONSTRAINT 🐘1 CHECK (name = '🐘');
CREATE TABLE
ALTER TABLE
postgres=# \dcs 🐘*
/******** QUERY *********/
SELECT n.nspname AS "Schema",
       cst.conname AS "Name"
FROM pg_catalog.pg_constraint cst
     JOIN pg_catalog.pg_namespace n ON n.oid = cst.connamespace
     JOIN pg_catalog.pg_class c on c.oid = cst.conrelid
WHERE cst.conname OPERATOR(pg_catalog.~) '^(🐘.*)$' COLLATE
pg_catalog.default
  AND pg_catalog.pg_table_is_visible(cst.conrelid)
ORDER BY 1, 2;
/************************/

List of constraints
 Schema | Name
--------+------
 public | 🐘1
(1 row)

postgres=# \dcs+ 🐘*
/******** QUERY *********/
SELECT n.nspname AS "Schema",
       cst.conname AS "Name" ,
       pg_catalog.pg_get_constraintdef(cst.oid) AS "Definition",
       c.relname AS "Table"
FROM pg_catalog.pg_constraint cst
     JOIN pg_catalog.pg_namespace n ON n.oid = cst.connamespace
     JOIN pg_catalog.pg_class c on c.oid = cst.conrelid
WHERE cst.conname OPERATOR(pg_catalog.~) '^(🐘.*)$' COLLATE
pg_catalog.default
  AND pg_catalog.pg_table_is_visible(cst.conrelid)
ORDER BY 1, 2, 4;
/************************/

                 List of constraints
 Schema | Name |         Definition          | Table
--------+------+-----------------------------+-------
 public | 🐘1  | CHECK ((name = '🐘'::text)) | zoo
(1 row)

postgres=# \dcs
/******** QUERY *********/
SELECT n.nspname AS "Schema",
       cst.conname AS "Name"
FROM pg_catalog.pg_constraint cst
     JOIN pg_catalog.pg_namespace n ON n.oid = cst.connamespace
     JOIN pg_catalog.pg_class c on c.oid = cst.conrelid
WHERE n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(cst.conrelid)
ORDER BY 1, 2;
/************************/

   List of constraints
 Schema |      Name
--------+-----------------
 public | zoo_id_not_null
 public | zoo_pkey
 public | 🐘1
(3 rows)


Thanks

Best, Jim

Tatsuro Yamada <yamatattsu@gmail.com> writes:
> I noticed that when the "+" (verbose option) is not used, the table name is
> not needed. In that case, joining the pg_class table is unnecessary.

A couple of drive-by comments:

* I don't like the name "\dcs": "cs" is a very strange choice of
abbreviation for "constraint".  Now, psql already has "\dc" and
"\dconfig", so our options are pretty limited, but I think that
"\dcn" would be least surprising.  It's typical to drop vowels
when abbreviating, so dropping the "o" makes sense (and we'd be
too close to "\dconfig" if we don't).  But I don't see the
argument for skipping "n" to seize on the next consonant "s".

* You might do well to use upper-case letters for add-on
filters (that is, "N" not "n" for not-null, etc).  This isn't
really a convention we've used elsewhere, except for the case
of "S" for "system" in some commands.  But I think it's too
hard to tell the base command name from the filter add-ons
without some typographic help like that.  Also, with this
being next door to "\dconfig" which is often abbreviated as
"\dcon", it seems like "\dcsn" or "\dcnn" would look too much
like typos for that.

* What about constraints on types (primarily, domain check
constraints)?  I'm not insisting that this patch support that,
but it'd be smart to take some thought now for how a future
command for that could be wedged into the command namespace.

* Not sure about column order of the output:

 Schema |            Name            |      Definition      | Table
--------+----------------------------+----------------------+-------

I find that just really odd.  Why not "Schema | Table | Name |
Definition"?  This also touches on whether we really need a "+" form
at all, rather than just always printing all four columns, because
I'm not sure I buy that you can infer the table name from the
constraint name.  Yes, it probably works okay for constraints that
were given default names, but for user-specified names maybe not so
much.  There's also the point that we don't guarantee that constraint
names are unique per-schema, only per-table.

            regards, tom lane

I wrote:
> * You might do well to use upper-case letters for add-on
> filters (that is, "N" not "n" for not-null, etc).  This isn't
> really a convention we've used elsewhere, except for the case
> of "S" for "system" in some commands.  But I think it's too
> hard to tell the base command name from the filter add-ons
> without some typographic help like that.

Or we could invert that: make the base command name "\dCN"
and then the add-on filters stay lower case.  Since we
already have, eg, "\dC", this might be less of a departure
from existing practice.

            regards, tom lane

> On Jan 15, 2026, at 14:32, Tatsuro Yamada <yamatattsu@gmail.com> wrote:
>
> Hi Tom and Jim,
>
> >The next patch will include the following changes:
> >
> >- Rename the command from \dcs to \dCN (proposed by Tom. Thanks!)
> >- Join pg_class in the query only when the "+" option is used
> >  (identified through Jim's additional testing. Thanks!)
>
> I have prepared a new patch and am sending it here.
> Please find the attached file.
>
>
> >Note: The following two points, which were discussed in the previous email,
> >will be addressed once the discussion is settled:
> >
> >- Changing the column order displayed by the \dCN command
> >- Adding domain constraints to the list of displayed items
>
> I have shared my thoughts on the two points raised by Tom in the email
> below, and I would appreciate your comments.
>
>     https://www.postgresql.org/message-id/CAOKkKFuYfdvpQ7eSYWxB1YrzwVafWm%2B%2BctXBPe_Rb1YqeOKjjA%40mail.gmail.com
>
> Regards,
> Tatsuro Yamada
> <v3-0001-Add-list-constraints-meta-command-dCN-on-psql.patch>

Hi Tatsuro-san,

Thanks for the patch. I just reviewed the patch, and I think it is solid already, just got a few small comments:

1 - commit message
```
\dCN shows all kind of constraints by using pg_constraint.
```

All kind -> all kinds


2.
```
+                    switch (cmd[3])
+                    {
+                        case '\0':
+                        case '+':
+                        case 'S':
+                        case 'c':
+                        case 'f':
+                        case 'n':
+                        case 'p':
+                        case 't':
+                        case 'u':
+                        case 'e':
+                        case 'x':
+                            success = listConstraints(&cmd[3], pattern, show_verbose, show_system);
+                            break;
+                        default:
+                            status = PSQL_CMD_UNKNOWN;
+                            break;
```
and
```
+    if (strlen(contypes) != strspn(contypes, dCN_options))
+    {
+        pg_log_error("\\dCN only takes [%s] as options", dCN_options);
+        return true;
+    }
```

These two checks are redundant. Is it intentional to keep both? It might be simpler to rely solely on listConstraints()
forvalidation. 

3
```
---- \dCN doesn't show constraints related to domain,
---- since \dD can be used to check them
```

I saw this in the test script, should we mention that in the doc change?

4
```
+        <term><literal>\dCN[cfnptue][Sx+] [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link>]</literal></term> 

```

Would it make sense to add a white space between [cfnptue] and [Sx+]? As you do so for the help message.

5
```
+    if (strlen(contypes) != strspn(contypes, dCN_options))
+    {
+        pg_log_error("\\dCN only takes [%s] as options", dCN_options);
+        return true;
+    }
+
+    if (pset.sversion < 180000)
+    {
+        char        sverbuf[32];
+
+        pg_log_error("The server (version %s) does not support this meta-command on psql.",
+                     formatPGVersionNumber(pset.sversion, false,
+                                           sverbuf, sizeof(sverbuf)));
+        return true;
+    }
```

Why return true after pg_log_error?

6
```
+    if (!(showCheck || showForeign || showNotnull || showPrimary || showTrigger || showUnique || showExclusion))
+        showAllkinds = true;
```

Nit: this might be simplified as: showAllkinds = !(showCheck || …), then you don’t need to initialize showAllkinds as
false.


Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

> On Jan 15, 2026, at 17:20, Tatsuro Yamada <yamatattsu@gmail.com> wrote:
>
> Hi Chao-san,
>
> Thanks for your comments!
>
> On Thu, Jan 15, 2026 at 4:20 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> >3
> >```
> >---- \dCN doesn't show constraints related to domain,
> >---- since \dD can be used to check them
> >```
> >
> >I saw this in the test script, should we mention that in the doc change?
>
> Tom also commented on constraints related to domains, and I would like to
> discuss and decide whether to include them in the \dCN command.
>
> Depending on the decision, I am considering the following changes:
>
> - If they are included:
>     - Remove the above test case from the regression tests
> - If they are not included:
>     - Add the reason for excluding them to the documentation
>
> Which do you think is better? Should domain constraints be covered by \dCN?
> I would appreciate your feedback.

I had a feeling while reviewing the patch but I didn’t raise it because I was not firm minded. As you ask, this is just
mypersonal opinion, you may ignore if you don’t consider reasonable. 

This patch claims “constraints”, but it actually only shows table constraints. How, we can see the code comment says
"Describesconstraints”, the command message says "List of constraints”, I think that’s where the discussion came from. 

Maybe an easy way to go is something like renaming the command to \dTCN, making it specific to table constraints.

>
> >6
> >```
> >+       if (!(showCheck || showForeign || showNotnull || showPrimary || showTrigger || showUnique || showExclusion))
> >+               showAllkinds = true;
> >```
> >
> >Nit: this might be simplified as: showAllkinds = !(showCheck || …), then you don’t need to initialize showAllkinds
asfalse. 
>
> Personally, I do not think there is any major issue with the current code,
> but since you suggested it, I will simplify it.
>

Yeah, I marked it as “nit”.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

> On Jan 15, 2026, at 17:53, Chao Li <li.evan.chao@gmail.com> wrote:
>
>
>
>> On Jan 15, 2026, at 17:20, Tatsuro Yamada <yamatattsu@gmail.com> wrote:
>>
>> Hi Chao-san,
>>
>> Thanks for your comments!
>>
>> On Thu, Jan 15, 2026 at 4:20 PM Chao Li <li.evan.chao@gmail.com> wrote:
>>
>>> 3
>>> ```
>>> ---- \dCN doesn't show constraints related to domain,
>>> ---- since \dD can be used to check them
>>> ```
>>>
>>> I saw this in the test script, should we mention that in the doc change?
>>
>> Tom also commented on constraints related to domains, and I would like to
>> discuss and decide whether to include them in the \dCN command.
>>
>> Depending on the decision, I am considering the following changes:
>>
>> - If they are included:
>>    - Remove the above test case from the regression tests
>> - If they are not included:
>>    - Add the reason for excluding them to the documentation
>>
>> Which do you think is better? Should domain constraints be covered by \dCN?
>> I would appreciate your feedback.
>
> I had a feeling while reviewing the patch but I didn’t raise it because I was not firm minded. As you ask, this is
justmy personal opinion, you may ignore if you don’t consider reasonable. 
>
> This patch claims “constraints”, but it actually only shows table constraints. How, we can see the code comment says
"Describesconstraints”, the command message says "List of constraints”, I think that’s where the discussion came from. 
>
> Maybe an easy way to go is something like renaming the command to \dTCN, making it specific to table constraints.

I just want to add that, I am not sure if “table constraints” is a proper group, and I didn’t intend to suggest a new
commandname as \dTCN. My point was to make the feature scope more specific, and make the command name better reflect to
thescope. 

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Hello Tatsuro,

On 2026-Jan-16, Tatsuro Yamada wrote:

> As you pointed out, it was indeed inappropriate to exclude some types of
> constraints while using the title "list constraints."
> To address this issue, I have modified the query to include domain
> constraints in the output. This is included in the v5 patch.
> 
> The revised query is shown below.
> The main difference from the v4 patch is that the pg_type table is now
> joined.

I wonder if the double LEFT JOIN is best, or you should instead join
pg_constraint twice and UNION ALL them, like

SELECT n.nspname AS "Schema",
       cns.conname AS "Name" ,
       pg_catalog.pg_get_constraintdef(cns.oid, true) AS "Definition",
       cns.relname AS "Table"
FROM ( select cns1.*, NULL as relname from pg_catalog.pg_constraint AS cns1
            JOIN pg_catalog.pg_type t ON t.oid = cns1.contypid
        union all                                          
       select cns2.*, c.relname from pg_catalog.pg_constraint AS cns2
            JOIN pg_catalog.pg_class c on c.oid = cns2.conrelid
     ) AS cns                          
     JOIN pg_catalog.pg_namespace n ON n.oid = cns.connamespace
WHERE n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4 ;

I think knowing what's best is going to require testing with a large
number of constraints to see which one scales best.  (The million table
challenge!).

Also, if you're going to show type constraints, I think you should show
the type name.

Tom mentioned the issue of sort order.  So there are two things, one is
where the column appears.  You have

 Schema │     Name    │ Definition    │ Table                

and he suggested
 Schema │     Table   |       Name    │ Definition

now if we add the type, we could do
 Schema │     Table   |    Type   |    Name    │ Definition

I kinda agree that Definition, being potentially arbitrarily long,
should go at the end.  Not sure about where to put constraint name vs.
table name.  The other ordering issue is the ORDER BY clause -- makes me
wish we had a way to choose whether to sort by table/type name or by
constraint name.

I like the idea of distinguishing filter spec from the base command name
using upper/lowercase -- the "\dCN[cfnptue]" part.

I agree with another reviewer that said that having exec_command_d()
check only the third char is kinda pointless.  Just let
listConstraints() check everything seems easiest and more consistent.

I don't think making the Table column be conditional on whether + is
given is useful.  I mean, what can do you with a constraint list if you
don't know where each applies?

There should also be a "Constraint type" column (check, FK, etc), if
more than one type of constraints is displayed.  Perhaps that column
disappears if you have the + option (since it would be redundant with
the definition).


I was thinking the pattern system is too simplistic.  But after looking
again, I think the problem is that your tests don't include more
complicated cases like listing constraints in matching schemas and
matching tables, something like

\dCN cust*.order*
(list constraints in schemas matching cust* and tables matching order*)


Regards

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
Finally, the phrase, 'No one was ever fired for buying an IBM' I don't believe
has ever been translated into German."                       (Leonard Tramiel)

On 2026-Jan-19, Tatsuro Yamada wrote:

Hello,

> # Summary
> The following changes are planned for the next patch:
> 
> - Changed the query (using UNION ALL)
> - Changed the columns and their order (and the sort order accordingly):
> - Schema | Table | Type | Name | Definition
> - Toggle definition verbosity with the + option
> - Added a test case: \dCN cust*.order*
> 
> The following items will not be included for now (as they are not critical):
> 
> - Option to switch sort order
>   (e.g., sort by constraint name vs. table name)
> - Improved command name checking logic

Yeah, that sounds good to me.

Thanks!

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda enseñar algo." (Jean B. Say)

On 2026-Jan-20, Tatsuro Yamada wrote:

> The error in the query has been fixed, and the execution times were
> measured again.
> As a result, there was no significant difference in execution time
> between the two queries (see the attached test_result2.txt).
> When comparing the cost of the top node, the original query has a
> slightly lower cost, so I decided to keep it as is.

Yeah, these results match my own tests -- the query I proposed is a bit
slower.  Not by a lot, but measurably so.  (I did ran my disk full by
running pgbench with the two queries in separate files and the one
million constraints; temp files for sort are quite large.  My query is
pretty consistently reported to be slower than the other anyway.)

>                    List of constraints
>  Schema | Table |   Type    |            Name
> --------+-------+-----------+----------------------------
>  public | con_c | NOT NULL  | con_c_primary_col_not_null
>  public | con_c | PK        | con_c_pkey
>  public | con_p | CHECK     | con_p_check_col_check
>  public | con_p | EXCLUSION | con_p_exclusion
>  public | con_p | FK        | con_p_foreign_col_fkey
>  public | con_p | NOT NULL  | con_p_notnull_col_not_null
>  public | con_p | NOT NULL  | con_p_primary_col_not_null
>  public | con_p | PK        | con_p_pkey
>  public | con_p | TRIGGER   | con_p_trigger
>  public | con_p | UNIQUE    | con_p_unique_col_key
> (10 rows)
> 
> What do you think?

Looks good.  I think the constraint types ought to be lower-case and
fully spelled out, with not-null having an hyphen as discussed
elsewhere (though I couldn't find the reference), so
    not-null
    primary key
    exclusion
    check
    foreign key
    unique
    trigger

I wonder if we should mark temporal constraints especially.  (So
"temporal primary key", etc).  Without thinking too much about it, my
impression is that it'd be useful.

I didn't look at the patch closer, apologies.

Thanks

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Officer Krupke, what are we to do?
Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")

Hi Álvaro,

Thanks for your comments!

On Tue, Jan 20, 2026 at 9:30 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:
> Yeah, these results match my own tests -- the query I proposed is a bit
> slower.  Not by a lot, but measurably so.  (I did ran my disk full by
> running pgbench with the two queries in separate files and the one
> million constraints; temp files for sort are quite large.  My query is
> pretty consistently reported to be slower than the other anyway.)

I see that you used pgbench to compare the throughput of the two
queries. I’m glad that you obtained consistent results regardless of
the individual environment.


> > What do you think?
>
> Looks good.  I think the constraint types ought to be lower-case and
> fully spelled out, with not-null having an hyphen as discussed
> elsewhere (though I couldn't find the reference), so
>         not-null
>         primary key
>         exclusion
>         check
>         foreign key
>         unique
>         trigger

Thanks.
I agree that all constraint types should be displayed in full and
in lowercase.


> I wonder if we should mark temporal constraints especially.  (So
> "temporal primary key", etc).  Without thinking too much about it, my
> impression is that it'd be useful.

I was not aware of temporal constraints.
Based on the article [1], I ran a quick test on temporal primary keys,
and they appear to be displayed correctly as primary keys:

-- Create employees table with temporal constraints
CREATE TABLE employees (
emp_id INTEGER,
emp_name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
position VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL,
valid_period tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)'),
-- Temporal primary key: no overlapping periods for same employee
PRIMARY KEY (emp_id, valid_period WITHOUT OVERLAPS)
);

# \dCNp+
List of constraints
Schema | Table | Type | Name | Definition
--------+----------+-------------+----------------+----------------------------------------------------
public | employees | primary key | employees_pkey | PRIMARY KEY
(emp_id, valid_period WITHOUT OVERLAPS)
(1 row)

I will investigate other types of temporal constraints and consider
adding them to the test cases. Thank you for pointing that out.

The next patch will include the following two fixes:
  - Display all constraint types in full and in lowercase.
  - Add test cases for temporal constraints.

[1] https://neon.com/postgresql/postgresql-18/temporal-constraints

Regards,
Tatsuro Yamada

Hi Álvaro and all,

> The next patch will include the following two fixes:
>   - Display all constraint types in full and in lowercase.
>   - Add test cases for temporal constraints.

The above two issues have been addressed in the V7 patch.
Below is an example output.

                             List of constraints
 Schema |      Table      |    Type     |                Name
--------+-----------------+-------------+------------------------------------
 public | temporal_con_fk | foreign key | temporal_con_fk_p_id_valid_at_fkey
 public | temporal_con_pk | not-null    | temporal_con_pk_id_not_null
 public | temporal_con_pk | not-null    | temporal_con_pk_valid_at_not_null
 public | temporal_con_pk | primary key | temporal_con_pk_pkey
 public | temporal_con_uq | unique      | temporal_con_uq_id_valid_at_key
(5 rows)

Please find the attached file.

Regards,
Tatsuro Yamada

Hi Chao, Álvaro, and all,

>>I agree with another reviewer that said that having exec_command_d()
>>check only the third char is kinda pointless.  Just let
>>listConstraints() check everything seems easiest and more consistent.
>
>As I mentioned in a previous email, the reason exec_command_d() only
>checks the third character is that it follows the same implementation
>pattern as other existing commands.
>
>While I agree that this could be improved, since the current behavior is
>consistent with other commands, I do not consider it critical at the moment.
>If I were to change it, I would likely postpone that improvement to a later
>patch.

After reviewing the code, I realized that the comment about passing
everything to listconstraints() instead of just checking the third string
was correct. Therefore, I updated the code accordingly.
Thanks to Chao and Alvaro for their comments.

I created the new patch (v8).

Other fixes included:
- Updated the documentation to clarify what the pattern string matches.
- Modified the case statement to use a predefined variable when converting
  contypes to strings.

Please find the attached file.

Thanks,
Tatsuro Yamada